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 Application then under this we need to refer 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 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 RANGE object.

Code:

Sub Range_Examples()

 Range(

End Sub

As you can see above 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 RANGE object.

If we want to insert the value to the cell then we need to choose “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 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 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 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 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 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 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