10 ways to reference cells using range object in excel vba

how-to-use-range-object-in-vba

Range Object

Range object mainly used for referencing cells in excel. This object has its own properties and methods to use, but only value and offset property is focused on this. Some of the other properties are count,rows.count, columns count, select, row, column, etc.we will see more on these properties in the future articles.

Let us see various methods of referencing cells in excel VBA,

Method 1:

Code: range("A1").Value = "Hello"

how-to-use-range-object-in-vba

The above code prints the word "Hello" in the cell A1.

Method 2:

Code: range("A1,C3").Value = "Hello"

how-to-use-range-object-in-vba

As you can see, the above code prints the word "Hello" on two individual cells A1 and C3.

Method 3:

Code: range("A1", "E1").Value = "Hello"

how-to-use-range-object-in-vba

As you can see, the above code prints the word "Hello" starting from A1 to E1, note the " " in method 2 and method 3 which looks similar but not.

Method 4:

Code: range("A" & 1, "C" & 5) = "Hello"

how-to-use-range-object-in-vba

This method is same as method 3 but we alter it differently to make it dynamic, this method is mainly useful if you are going to reference cells dynamically through iteration methods like (for loop) etc.

Method 5:

Code: range("A1:C6").Value = "Hello"

how-to-use-range-object-in-vba

This method targets groups of cells, say that if we want to print the word "Hello" starting from cell A1 to C6 we use this method. That A1 to C6 is called a range.

Method 6:

Code: range("A1:C6,E1:G6").Value = "Hello"

how-to-use-range-object-in-vba

As you can see this method is the same as method 5 but we target more ranges through this.

Method 7:

Code: range("A4:C7").Cells(4, 2).Value = "Hello"

how-to-use-range-object-in-vba

Actual syntax to this method is range("row index:Column index").Cells(row index,Column index).Value = "Hello".

This method is same as method 5 but we target a particular cell inside a range A4: C7, cells property takes row index and column index as arguments example in the above code in .cells(4,2) row index - 4 and column index - 2 and index starts from 1 here.

Having said that you can see that the word "Hello" is printed at 4th row and 2nd column inside the range A4: C7.

Method 8:

Code: range("A1").Offset(5, 2).Value = "Hello"

how-to-use-range-object-in-vba

Actual syntax for offset property is range("A1").Offset(row index,Column index).Value = "Hello".Index in this property starts from 0.

As you can see the above code prints the word "Hello" at 5th-row index and 2nd column index.

Method 9:

Code: range("A1:B1").Offset(7, 1).Value = "Hello"

how-to-use-range-object-in-vba

As you can see, the above code is the same as method 8 except we target in range here rather than a particular cell.

The above code prints the first "Hello" in the 7th-row index and 1st column index because for the cell A1 column index is 1 at column B and, but for the cell B1 column C is the first column as the cell B1 column index is 0.

Method 10:

Code: range("myvar").Value = "Hello"

how-to-use-range-object-in-vba

In this method, we assign a name to a range and use that name to reference cells dynamically. As you can see I named the range A10: D10 as myvar and used that name to reference cells.

The above codes are just the basics,but you make these codes work more dynamically through conditional statements and iteration statements in VBA.We will be seeing more on range object in future posts.

Share on:
Most Popular