VBA Select Cell

Excel VBA Select Cell

When we work with Excel, we work with cells; so, to automate tasks, we need to work with cells in VBA. Select is a method used to select a cell in VBA, worksheets, workbooks, and other objects.

Cells can be selected by using VBA Select Cell method along with the Range object and Cells property. In the worksheet, we select the cells with just a mouse click. In VBA, we need to give the cell address in double quotes using either the RANGE object or CELLS property and then use the select method.

For example, if we want to select cell A2, then, we can first refer to the cell address by using the RANGE object as shown in the following image and choose the select method of the referenced cell.

This will select cell A2 in the worksheet.

How To Select Excel Cell Using VBA?

There are various ways to select a cell in VBA. Let us learn some of the techniques to select a cell in VBA.

#1 – Select Cell Through Macro Recorder

If you are new to VBA, then the best way to start your journey is by recording a macro. Record macro will record the code for you. Then, you can learn about code writing on your own. Follow the steps listed below.

Step 1 – Go to the Developer tab and click on Record Macro.

Step 2 – This will ask us to name the macro. Give a name to the macro, such as Select_Cell.

Step 3 – Click on OK, and the recording will start in our Excel. Now, select cell B5 with a mouse click.

Step 4 – Don’t do anything apart from selecting the cell. Go to the Developer tab and click on Stop Recording.

Now, the recording is stopped. To view how the code is generated, go to the Developer tab and click on Visual Basic.

In Module1, we have the following code.

We have selected cell B5, so VBA recorded the code as Range(“B5”).Select

To reference cell B5, it has used the RANGE object, and inside the RANGE object, it has given the cell address (B5) in double quotes and then chooses the select method.

To test how this works, first, select any of the cells other than cell B5.

We have selected cell A1 here.

Go to the Developer tab and click on the Macros option.

It will bring the list of VBA macros available in the workbook. Choose the macro name Select_Cell and click on Run.

After clicking on the Run button, we can see that it has selected cell B5.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#2 – Select Cell Of The Active Worksheet With VBA In Excel

We can also select by using the CELLS property of VBA. In VBA, whichever sheet is active is considered an active sheet.

Assume we need to select cell B3 of the active sheet, then, first, we need to use the active sheet property. Enter the word active, and we can see the IntelliSense list showing the active sheet.

Choose the ActiveSheet from the list and then enter the dot.

The problem with using the active sheet method is we will not see the IntelliSense list of properties and methods associated with the active sheet object.

After the dot, enter the CELLS property.

Since we need to select cell B3, we cannot really enter the cell address here because the CELLS property uses the R1C1 cell reference.

The B3 cell address row number is 3, and the column number is 2. So, enter the row number as 3 and the column number as 2, each separated by a comma.

Finally, enter the dot and enter the select method.

This will select cell B3 in the active worksheet.

#3 – Select Cells Using Range Object

Using the CELLS property, we can select only one cell, but using the RANGE object, we can select multiple cells and play around with multiple cells at a time.

For example, assume we need to select the range of cells A1:B10. Then, we need to open the RANGE object in the sub-procedure.

In the double quotes, provide the cell address i.e., A1:B10.

Enter the dot, and we will see an IntelliSense list.

This is the advantage of using the RANGE object because, as a new VBA learner, the IntelliSense list helps us a lot.

Choose or enter the Select method.

This will select the range of cells A1 to B10 in the active worksheet.

#4 – Select Cell Of The Active Workbook But Not Of The Active Worksheet With VBA In Excel

We have seen if we do not specify any of the worksheet names, it will select the given cell address in the active worksheet i.e., whichever worksheet is active in the active workbook.

However, if we want to select the cell in different worksheets, then, we need to provide the worksheet name using the WORKSHEETS object or SHEETS object.

For example, assume we need to select the range of cells A1:C5 in the worksheet Sheet2; then, we need to enter the worksheet name as shown in the following image.

Enter the dot and enter the RANGE object name, and open parenthesis.

Inside the RANGE object, provide the desired cell address in double-quotes.

Enter the dot and enter the select method.

Right now, we are on worksheet Sheet1.

Now, let’s run the code.

We can see the following error Select method of range class failed.

The reason for this is, whenever we try to select the cells in different worksheets (other than active worksheets), we cannot really select the cells from the different worksheets.

First, we need to select or activate the desired worksheet and then select the cells of that worksheet.

Look at the following code.

Sub Example3_Select_Cell()

    ‘Activate the desired worksheet first

    Worksheets(“Sheet2”).Activate

    ‘Select the desired range of cells in that worksheet

    Worksheets(“Sheet2”).Range(“A1:C5”).Select

 End Sub

First, we are activating the desired worksheet by using the below code.

Worksheets(“Sheet2”).Activate

Next, we select the desired range of cells in the activated worksheet by using the following code.

Worksheets(“Sheet2”).Range(“A1:C5”).Select

OR

Range(“A1:C5”).Select

This will select the cell range A1:C5 in the worksheet Sheet2.

#5 – Select Cell Out Of The Active Workbook With VBA In Excel

We can also select the cells in workbooks apart from the active workbook. Assume, we have two workbooks opened at a time named Book1 and Book2.

Right now, we are in the Book1 workbook, and we need to select the cells in the range A1:C5 in the Book2 workbook.

Then we can write the code as highlighted below.

Sub Example4_Select_Cell()

Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1:C5").Select

End Sub

Workbooks(“Book2.xlsx”).

First, we are referencing the workbook name by using the WORKBOOKS object, and inside the workbooks object, we have given the workbook name Book2 followed by the workbook file extension xlsx.

Worksheets(“Sheet1”).

Then in that workbook, we have given the reference of the worksheet Sheet1.

Range(“A1:C5”).Select

Then, we select the range of cells A1:C5.

Now, let’s run this code.

We have got the error Select method of range class failed.

The error occurred since we are in workbook Book1 and we are trying to select the range of cells in a different workbook without activating that workbook.

To make this code work without any error, first, we need to activate the workbook and then choose the range of cells in that workbook by mentioning the worksheet name.

The following code will work properly.

Sub Example4_Select_Cell()

‘Activate the desired worksheet first
Workbooks(“Book2.xlsx”).Activate

'Select the desired range of cells in that workbook
Worksheets("Sheet1").Range("A1:C5").Select

End Sub

This should select the range of cells A1 to C5 in the workbook Book2 and worksheet Sheet1.

#6 – Select Cell Of A Named Range With VBA In Excel

VBA works with the named range as well. For example, look at the following data in Excel.

Select the data range from A1 to B6 and give a name to it as Sales_Data in the name box.

Now, whenever we need to select the range A1 to B6, we can simply use the name Sales_Data.

Sub Example5_Select_Cell()

Range(“Sales_Data”).Select 

End Sub

This will select the range of cells A1 to B6.

Now if we want to select the cells within this named range, we need to use the CELLS property.

For example, if we want to select cell B3 within this named range, then, we can use the following code.

Sub Example5_Select_Cell()

Range(“Sales_Data”).Cells(3, 2).Select

End Sub

This will select cell B3.

#7 – Select Cell Relative To Another Cell With VBA In Excel

We can select the cell relative to another cell using the OFFSET function in VBA. For example, we are in cell B2, and if we need to select cell C6, then we need to move down 4 rows and move 1 column right.

The following code will select cell C6 from cell B2.

Sub Example5_Select_Cell()

Range(“B2”).Offset(4, 1).Select

End Sub

This will select cell C6 from cell B2.

Important Things To Note

• Select is a method available to select objects in Excel.
• If the code begins with a single quote, then it will be treated as a comment, not as code.
• Active Sheet is nothing but the current worksheet, which is active in the workbook.
• Whenever we need to select the cells of the different worksheets of the active workbook, we need to first activate or select that worksheet first and then write a code to select the required range of cells.
• Similarly, to select the cells from different workbooks, first, we need to activate the workbook and then select the cells from the desired worksheet of that workbook.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Select Cell, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Select Cell. Here we explain how to select excel cell using VBA with examples and downloadable excel template. You may learn more from the following articles –

• VBA UBOUND
• VBA Subscript Out of Range
• VBA Macros