In this article, I’ll show you how you can select a cell or a range of cells with VBA in Excel. You’ll learn to select a single, a range of cells, a cell with a named range, and a cell related to another cell with VBA.
Download Practice Workbook
6 Useful Ways to Select Cell with VBA in Excel
Let’s explore the 6 most useful methods to select a cell or a range of cells with VBA.
1. Select Cell of the Active Worksheet with VBA in Excel
First of all, let’s select a cell of the active worksheet with VBA in Excel.
Here I’ve got a workbook called Workbook1. There are three worksheets called Sheet1, Sheet2, and Sheet3 in the workbook. The active worksheet is Sheet1.
You can use the following line of code to select any cell (C5 in this example) in the active worksheet:
⧠VBA Code:
ActiveSheet.Range("C5").SelectOr,
ActiveSheet.Cells(5,3).Select
⧠Output:
Run it. And it will select cell C5 of the active worksheet Sheet1 of Workbook1.
2. Select Cell of the Active Workbook but not of the Active Worksheet with VBA in Excel
Now, let’s select a cell of the active workbook, but not of the active worksheet. Our active worksheet is Sheet1, but this time we’ll select cell C5 of Sheet2.
You can use the following line of code:
⧠VBA Code:
Application.Goto Sheets("Sheet2").Range("C5")Or,
Application.Goto Sheets("Sheet2").Cells(5,3)Or,
Sheets("Sheet2").Activate
Range("C5").Select
⧠Output:
Run it. And it will select cell C5 of the worksheet Sheet2 of the active workbook Workbook1.
3. Select Cell Out of the Active Workbook with VBA in Excel
This time we’ll select a cell, not from the active workbook.
Our active workbook is Workbook1. But we have another workbook called Workbook2 in the same folder.
Let’s select cell C5 of Sheet1 of Workbook2.
The line of VBA code will be:
⧠VBA Code:
Application.Goto Workbooks("Workbook2.xlsx").Sheets("Sheet1").Range("C5")Or,
Application.Goto Workbooks("Workbook2.xlsx").Sheets("Sheet1").Cells(5,3)Or,
Workbooks("Workbook2.xlsx").Activate
Sheets("Sheet1").Select
⧠Output:
Run the code and it’ll select cell C5 of Sheet1 of Workbook2.
4. Select a Range of Cells with VBA in Excel
Up till now, we’ve selected only a single cell.
This time we’ll select a range of cells (Let’s say B4:C13 in this example).
If it’s of the active worksheet, you can use:
⧠VBA Code:
Range("B4:C13").Select
⧠Output
It’ll select cells B4:C13 of the active worksheet Sheet1 of Workbook1.
If it’s of the active workbook, but not of the active worksheet (Sheet2 in this example), use:
⧠VBA Code:
Application.Goto Sheets("Sheet2").Range("B4:C13")
⧠Output:
It’ll select cells B4:C13 of Sheet2 of the active workbook Workbook1.
And if you want to select a range of cells from a workbook that’s not active (Workbook2 in this example), use this line of code:
⧠VBA Code:
Application.Goto Workbooks("Workbook2.xlsx").Sheets("Sheet2").Range("B4:C13")
⧠Output:
It’ll select the range B4:C13 of Sheet1 of Workbook2.
5. Select Cell of a Named Range with VBA in Excel
You can also select one or more cells of a Named Range with VBA in Excel.
Here in the active sheet Sheet1 of Workbook1, we’ve got a Named Range called ABC which consists of the range B4:C13.
To select the Named Range ABC, use this line of code:
⧠VBA Code:
Range("ABC").Select 
⧠Output:
It’ll select the Named Range (B4:C13) of Sheet1 of Workbook1.
6. Select Cell Relative to Another Cell with VBA in Excel
Finally, you can select a cell relative to another cell with VBA.
You can use the Offset property of VBA for this purpose.
For example, let’s select the cell to 2 rows down and 3 columns right from cell C5 in the active worksheet Sheet1 of Workbook1.
Use the following line of code:
⧠VBA Code:
Range("C5").Offset(2, 3).SelectOr,
Cells(5,3).Offset(2, 3).Select⧠Output:
It’ll select cell F7, the cell to 2 rows down and 3 columns right from cell C5.
Conclusion
Using these methods, you can select a cell or a range of cells with VBA in Excel. Do you have any questions? Feel free to ask us.
These are great instructions! Let’s say I have a horizontal range already selected by the user. For example it could be A2:G2. How can I know the rightmost cell of the selection range? In my example it would be G2. My macro will do something with the users’ current selection when invoked, but then I want to focus the cell on the rightmost cell.
Hello KEVIN,
Hope you are doing well. You can follow the procedures below to get the address of the rightmost cell.
Here, we have taken the following dataset into our consideration. Suppose, the user selected the header of the dataset which is A3:G3.
• Go to the Developer tab >> Visual Basic option to open the Visual Basic Editor window.
• Use the following code.
• Press F5.
Then, you will get the following message with the rightmost cell of the selected range $G$3.