VBA Set Range

Updated on January 2, 2024

Set Range in Excel VBA

Set range in VBA means we specify a given range to the code or the procedure to execute. If we do not provide a specific range to a code, it will automatically assume the range from the worksheet with the active cell. So, it is very important in the code to have a range variable set.

After working with Excel for so many years, you must have understood that all works we do are on the worksheet. In worksheets, it is cells containing the data. So when you want to play around with data, you must be a behavior pattern of cells in worksheets. So, when the multiple cells get together, it becomes a RANGE. Therefore, to learn VBA, you should know everything about cells and ranges. So in this article, we will show you how to set the range of cells we can use for VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more in detail.

VBA-Set-Range

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Set Range (wallstreetmojo.com)

What is the Range Object?

Range in VBA refers to an object. A range can contain a single cell, multiple cells, a row or column, etc.

In VBA, we can classify the range as below.

“Application >>> Workbook >>> Worksheet >>> Range”

First, we need to access the Application. Then under this, we need to refer to which workbook we are referring to. Next, in the workbook, we are referring to which worksheet we are referring to. Then in the worksheet, we need to mention the range of cells.

Using the Range of cells, we can enter the value to the cell or cells, we can read or get values from the cell or cells, we can delete, we can format, and we can do many other things as well.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

How to Access the Range of Cells in Excel VBA?

You can download this VBA Set Range Excel Template here – VBA Set Range Excel Template

In VBA coding, we can refer to the cell using the VBA CELLS propertyUsing VBA CELLS PropertyCells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells.read more and RANGE object. So, for example, if you want to refer to cell A1, we will first see using the RANGE object.

Inside the subprocedure, we need first to open the RANGE object.

Code:

Sub Range_Examples()

 Range(

End Sub
VBA Set Range Example 1

As you can see above, the RANGE object asks what cell we are referring to. So, we need to enter the cell address in double quotes.

Code:

Sub Range_Examples()

 Range ("A1")

End Sub
VBA Set Range Example 1-1

Once we supply the cell address, we must decide what to do with this cell using properties and methods. Now, put a dot to see the properties and methods of the RANGE object.

VBA Set Range Example 1-2

If we want to insert the value into the cell, we must choose the “Value” property.

Code:

Sub Range_Examples()

 Range("A1").Value

End Sub
VBA Set Range Example 1-3

To set a value, we need to put an equal sign and enter the value we want to insert into cell A1.

Code:

Sub Range_Examples()

 Range("A1").Value = "Excel VBA Class"

End Sub
VBA Set Range Example 1-4

Run the code through the run option and see the magic in cell A1.

VBA Set range Example 1.gif

As the code mentioned, we have the value in cell A1.

Similarly, we can also refer to the cell using the CELLS property. Open the CELLS property and see the syntax.

VBA Set Range Example 2

It is unlike the RANGE object, where we can enter the cell address directly in double quotes. Rather, we need to give a row number and column to refer to the cell. For example, since we are referring to cell A1, we can say the row is 1, and the column is 1.

VBA Set Range Example 2-1

After mentioning the cell address, we can use properties and methods to work with cells. But the problem here is unlike the Range object after putting a dot. We do not get to see the IntelliSense list.

VBA Set Range Example 2-2

So, it would help if you were an expert to refer to the cells using the CELLS property.

Code:

Sub CELLS_Examples()

 Cells(1, 1).Value = "Excel VBA Class"

End Sub
VBA Set Range Example 2-3

Accessing Multiple Cells & Setting Range Reference in VBA

One of the big differences between CELLS and RANGE is using CELLS. We can access only one cell but using RANGE. We can access multiple cells, as well.

For example, for cells A1 to B5, if we want the value of 50, we can write the code below.

Code:

Sub Range_Examples()

 Range("A1:B5").Value = 50

End Sub
VBA Set Range Example 3

It will insert the value of 50 from cells A1 to B5.

Insert values Example 3-1

Instead of referring to the cells directly, we can use the variable to hold the reference of specific cells.

First, define the variable as the “Range” object.

Code:

Sub Range_Examples()  

  Dim Rng As Range

End Sub
Dim Example 2-4

Once we define the variable as the “Range” object, we need to set the reference for this variable about what the cell addresses will hold the reference to.

To set the reference, we need to use the “SET” keyword and enter the cell addresses using the RANGE object.

Code:

Sub Range_Examples()

  Dim Rng As Range

  Set Rng = Range("A1:B5")

End Sub
set Example 3-2

The variable “Rng” refers to the cells A1 to B5.

Instead of writing the cell address Range (“A1:B5”), we can use the variable name “Rng.”

Code:

Sub Range_Examples()

  Dim Rng As Range

  Set Rng = Range("A1:B5")

  Rng.Value = "Range Setting"

End Sub
Value Example 3-3

It will insert the mentioned value from the A1 to the B5 cells.

VBA Set range Example 1.gif

Assume you want whatever the selected cell should be a reference, then we can set the reference as follows.

Code:

Sub Range_Examples()

  Dim Rng As Range

  Set Rng = Selection

  Rng.Value = "Range Setting"

End Sub
selection Example 3-4

This one is a beauty because if we select any of the cells and run it, it will also insert the value to those cells.

For example, we will select certain cells.

select cells Example 3-5

Now, we will execute the code and see what happens.

VBA Set Range Example 3-6

For all the selected cells, it has inserted the value.

Like this, we can set the range reference by declaring variables in VBADeclaring Variables In VBAVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more.

Things to Remember

  • The range can select multiple cells, but CELLS can select one cell at a time.
  • RANGE is an object, and CELLS is property.
  • Any object variable should be the object’s reference using the SET keyword.

This article is a guide to VBA Set Range. Here, we discuss setting the range of Excel cells used for reference through VBA code, examples, and a downloadable Excel template. Below you can find some useful Excel VBA articles: –