Rifat Hassan
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.
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.
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.
It’ll select the range of cells B4:D13.
Note: You can use the Range object directly without declaring it first, like:
Range("B4:D13").SelectAlso 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")Read More: Different Types of Cell References in Excel (With Examples)
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].
Read More: Excel VBA Examples with Cell Reference by Row and Column Number
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.
It’ll select cell D5.
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].
Read More: Example of Relative Cell Reference in Excel (3 Criteria)
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.
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].
Read More: How to Use Cell Value as Worksheet Name in Formula Reference in Excel
Similar Readings
- How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)
- Mixed Cell Reference in Excel (4 Examples)
- Reference Another Sheet in Excel (3 Methods)
- Difference Between Absolute and Relative Reference in Excel
- How to Use Table Reference with Excel VBA (20 Examples)
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.
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).
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")
It’ll select the multiple ranges together.
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]
Read More: Excel VBA: Get Cell Value from Another Workbook without Opening
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.
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))
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:
CellsOr to refer to an inactive worksheet (For example, Sheet2), use:
Worksheet("Sheet2").Cells
It’ll select the whole worksheet Sheet2.
Read More: How to Use Reference of Worksheets in Excel (5 Examples)
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.
Related Articles
- Creating and Copying Formula Using Relative References in Excel
- How to Reference a Cell from a Different Worksheet in Excel
- Relative Cell Reference in Excel (Detailed Analysis)
- How to Use Variable Row Number as Cell Reference in Excel
- Relative and Absolute Cell Address in the Spreadsheet
- How to Reference Cell by Row and Column Number in Excel (4 Methods)
- How to Use Relative Reference in Excel Macro (with Easy Steps)
