Cell Reference in Excel VBA (8 Examples)

In this article, I’ll show you how you can access a cell reference in VBA in Excel. You’ll learn to access a single cell, as well as multiple cells together.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


8 Ways to Refer to Cell Reference in Excel VBA 

Here we’ve got a data set with the Book Name, Book Types, and Price of some books of a bookshop called Martin Bookstore.

The data set lies in the range B4:D13 of the worksheet.

Data Set to Refer to Cell Reference in Excel VBA

Today our objective is to learn to refer the cell references of this data set with VBA.

Here are the 8 best ways to refer to a cell reference with VBA in Excel.


1. Refer to a Cell Reference by Using the Range Object in VBA in Excel

First of all, you can refer to a cell reference by using the Range object of VBA.

You can refer to both a single cell and a range of cells with the Range object.

For example, to access the single-cell B4, use the line of code:

Dim Cell_Reference As Range

Set Cell_Reference = Range("B4")

The following code selects cell B4.

VBA Code to Refer to Cell Reference in Excel VBA

It’ll select cell B4 in the active worksheet.

Similarly, you can access a range of cells in this way.

Dim Cell_Reference As Range

Set Cell_Reference = Range("B4:D13")

The following code selects the range B4:D13.

Select Cells to Refer to Cell Reference in Excel VBA

It’ll select the range of cells B4:D13.

Select Range to Refer to Cell Reference in Excel VBA

Note: You can use the Range object directly without declaring it first, like:

Range("B4:D13").Select

Also if you want to access any cell of a worksheet that’s not active, use the name of the worksheet before the Range object.

For example, to access the cell B4 of Sheet2, use:

Worksheets("Sheet2").Range("B4:D13")

2. Refer to a Cell Reference by Using the Index Numbers in VBA in Excel

You can also refer to a cell reference by using the Index numbers. But in this method, you can refer only to a single cell.

For example, to access the cell with row number 4, and column number 2 (B4), use:

Cells(4, 2))

The following code again selects cell B4 of the active worksheet.

It’ll select cell B4.

Note: To access any cell of an inactive worksheet, use the name of the worksheet before the cell reference.

For example:

Worksheets("Sheet2").Cells(4, 2)

[Same as method 1].


3. Refer to a Cell Reference Relative to Another Cell in VBA in Excel

You can also refer to a cell reference relative to another cell in VBA. You have to use the Offset function of VBA for this.

To access the cell 1 row down and 2 column right of the cell B4 (D5), use:

Range("B4").Offset(1, 2)

The following code selects cell D5 of the active worksheet.

VBA Code to Refer to Cell Reference in Excel VBA

It’ll select cell D5.

Selecting Cell to Refer to Cell Reference in Excel VBA

Note: To refer to any cell of a worksheet that’s inactive, use the name of the worksheet before the cell reference.

For example:

Worksheets("Sheet2").Range("B4").Offset(1, 2)

[Same as method 1 and 2].


4. Refer to a Cell Reference by Using the Shortcut Notation in VBA in Excel

There is a shortcut notation available to access any cell reference in VBA. You can refer to both a single cell and a range of cells in this way.

To access cell B4, use:

[B4] 

Or to access the range B4:D13, use:

[B4:D13]

The following code selects the range B4:D13.

It’ll select the range B4:D13.

Select Range to Refer to Cell Reference in Excel VBA

Note: To refer to any cell of an inactive worksheet, use the name of the worksheet before the cell reference.

For example:

Worksheets("Sheet2").[B4:D13]

[Same as methods 1, 2, and 3].


5. Refer to a Named Range in VBA in Excel

You can refer to a Named Range with VBA in Excel.

Let’s name the range B4:D13 of the active worksheet as Book_List.

Named Range to Refer to Cell Reference in Excel VBA

Now we can refer to this Named Range by the line of code:

Range("Book_List")

The following code selects the range Book_List (B4:D13).

VBA Code to Refer to Cell Reference in Excel VBA

It’ll select the range Book_List.

Note: To access any cell of an inactive worksheet, use the name of the worksheet before the cell reference.

For example:

Worksheets("Sheet2").Range("Book_List")

[Same as methods 1, 2, 3, and 4].


6. Refer to Multiple Ranges in VBA in Excel

You can also refer to multiple ranges in VBA in Excel.

To access the range B4:D5, B7:D8, and B10:D11, use:

Range("B4:D5,B7:D8,B10:D11")

VBA Code to Refer to Cell Reference in Excel VBA

It’ll select the multiple ranges together.

Selecting Multiple Ranges to Refer to Cell Reference in Excel VBA

Also, you can use the Union property of VBA to access multiple ranges together.

Union(Range("B4:D5"), Range("B7:D8"), Range("B10:D11"))

Or you can access multiple Named Ranges together.

Range("Named_Range_1,Named_Range_2")

Also, put the worksheet name in front of inactive worksheets.

For example:

Worksheets("Sheet2").Range("B4:D5,B7:D8,B10:D11")

[Same as methods 1, 2, 3, 4, and 5]


7. Refer to Rows and Columns in VBA in Excel

You can also refer to one or more rows or columns in VBA in Excel.

To access the 4th row, use:

Rows (4)

It’ll select the entire 4th row.

Selecting Row to Refer to Cell Reference in Excel VBA

Similarly, Columns(4) will access the entire 4th column.

And to access multiple rows or columns together, use the Union property of VBA.

To access the rows 4, 6, 8, and 10 together, use:

Union(Rows(4), Rows(6), Rows(8), Rows(10))

VBA Code to Refer to Cell Reference in Excel VBA

It’ll select the entire rows 4, 6, 8, and 10.

Note: Add the name of the worksheet in front in case it’s an inactive one.

For example:

Worksheets("Sheet2").Rows (4)

[Same as method 1, 2, 3, 4, 5, and 6]


8. Refer to the Whole Worksheet in VBA in Excel

Finally, I’ll show you to refer to the whole worksheet. To access the whole worksheet in VBA, use:

Cells

Or to refer to an inactive worksheet (For example, Sheet2), use:

Worksheet("Sheet2").Cells

It’ll select the whole worksheet Sheet2.

Selecting Whole Worksheet to Refer to Cell Reference in Excel VBA


Things to Remember

  • To access one or more cells of the active worksheet, you may mention the worksheet name in front or not, But to access cells of an inactive worksheet, you must mention the worksheet name in front of the cell reference.
  • Even you can access cells of an inactive workbook in VBA, In that case, you have to mention both the workbook name and the worksheet name in front of the cell reference.

Conclusion

Using these methods, you can refer to any cell reference with VBA in Excel. Do you have any questions? Feel free to ask us.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo