VBA Set Range

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 which has the active cell so it is very important in the code to have range variable set.

After working with excel so many years, you must have understood the fact that all works we do are on the worksheet, and in worksheets, it is cells that contain 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 are get together, it becomes a RANGE. To learn VBA, you should know each and everything about cells and ranges. So in this article, we will show you how to set the range of cells that can be used for VBA coding in detail.

What is the Range Object?

Range in VBA is referred to as 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, and in the workbook, we are referring to which worksheet we are referring to, and 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.

How to Access Range of Cells in Excel VBA?

In VBA coding, we can refer to the cell by using VBA CELLS property and RANGE object. For example, if you want to refer to the cell A1 then first, we will see using RANGE object.

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

Code:

Sub Range_Examples()

 Range(

End Sub

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

Code:

Sub Range_Examples()

 Range ("A1")

End Sub

Once the cell address is supplied, we need then decide on what to do with this cell by using properties and methods. Now put a dot to see the properties and methods of the RANGE object.

If we want to insert the value to the cell, then we need to choose the “Value” property.

Code:

Sub Range_Examples()

 Range("A1").Value

End Sub

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

Code:

Sub Range_Examples()

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

End Sub

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

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

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

This is unlike the RANGE object, where we can enter the cell address directly in double-quotes. Rather, we need to give row number and column to refer to the cell. Since we are referring to the cell A1, we can say the row is 1, and the column is 1.

After mentioning the cell address, we can use properties and methods to work with cells. But the problem here is unlike range object after putting dot. We don’t get to see the IntelliSense list.

So, you need to be an expert to refer to the cells by using CELLS property.

Code:

Sub CELLS_Examples()

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

End Sub

Accessing Multiple Cells & Setting Range Reference in VBA

One of the big differences between CELLS & 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, then we can write the code as below.

Code:

Sub Range_Examples()

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

End Sub

This will insert the value of 50 from cell A1 to B5.

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

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

Code:

Sub Range_Examples()  

  Dim Rng As Range

End Sub

Once the variable is defined as the “Range” object, we need to set the reference for this variable about what are the cell addresses are going to hold the reference to.

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

Code:

Sub Range_Examples()

  Dim Rng As Range

  Set Rng = Range("A1:B5")

End Sub

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

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

Code:

Sub Range_Examples()

  Dim Rng As Range

  Set Rng = Range("A1:B5")

  Rng.Value = "Range Setting"

End Sub

Now this will insert the mentioned value from A1 cell to B5 cell.

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

This is a beauty because if I select any of the cells and run, it will insert the value to those cells as well.

For example, I will select certain cells.

Now I will execute the code and see what happens.

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

Like this, we can set the range reference by declaring variables in VBA.

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 set the reference of the object by using the SET keyword.

Recommended Articles

This has been a guide to VBA Set Range. Here we discuss how to set the range of excel cells that can be used for reference through VBA code along with examples and a downloadable excel template. Below you can find some useful excel VBA articles –

• VBA Set
• VBA Sort Option
• Range Cells in VBA
• Using Range Objects in VBA
• VBA INT