How to Select Cell with VBA in Excel (6 Useful Ways)

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").Select

Or,

ActiveSheet.Cells(5,3).Select

VBA Code to Select Cell with VBA in Excel

Output:

Run it. And it will select cell C5 of the active worksheet Sheet1 of Workbook1.

Output to Select Cell with VBA in Excel


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.

Output to Select Cell with VBA in Excel


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

VBA Code to Select Cell with VBA in Excel

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

VBA Code to Select Cell with VBA in Excel

Output

It’ll select cells B4:C13 of the active worksheet Sheet1 of Workbook1.

Output to Select Cell with VBA in Excel

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.

Output to Select Cell with VBA in Excel

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")

VBA Code to Select Cell with VBA in Excel

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 

VBA Code to Select Cell with VBA in Excel

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).Select

Or,

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.


Related Readings

Rifat Hassan

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