Select Cell in Excel VBA
In excel we work with cells and range of the cell. In a regular worksheet, we can select the cell either by mouse or we reference the cell, as simple as that. However, in VBA, it is not that straight forward. For example, if we want to select the cell A1 using VBA, we cannot simply say “A1 cell”, rather we need to use the VBA RANGE object or CELLS property.
VBA coding is a language it has specifies a way of doing tasks. Selecting cells in one of those tasks which we need to script in the VBA language. In this article, we will show you how to select the cell in VBA.
How to Select Cell in Excel VBA?
Below are the examples to select a cell using VBA Code.
Example #1 – Select Cell through Macro Recorder
To start off the learning let’s start the process by recording the macro. Place a cursor on the cell other than the A1 cell.
I have selected the B3 cell as of now.
Now click on record macro button.
As soon as you click on that button you will see below a window. In this, you can give a new name or you can proceed with the default name by pressing the OK button.
Now we are in B3 cell, so select the cell A1.
Now stop the recording.
Click on Visual Basic to what it has recorded.
Now you will see the recording like this.
The only action we did while recording was we have selected the cell A1. So in VBA language to select any cell we need to use RANGE object, then specify the cell name in double quotes and use the SELECT method to select the specified cell.
Example #2 – Select Cells using Range Object in Excel VBA
Now by recording the macro, we get to know to select the cell we need to use the object RANGE. Now write on your own, type the word RANGE and open parenthesis.
Sub Macro1() Range( End Sub
Now it is asking what is the cell you want to refer in the range, type “A1”. Enter the cell address, close the bracket and type dot (.) to see all the properties and methods available with this cell.
Since we need to select the cell, type SELECT as the method.
Sub Macro1() Range("A1").Select End Sub
Place a cursor in the different cell and run this code to see how it selects the cell A1.
Example #3 – Insert Values to Cells
After selecting the cell what we usually do?
We perform some action, one action is we enter some value. We can enter the value in two ways, one is again using the RANGE object or use the object ActiveCell,
To insert value by using RANGE object, again refer the cell A1 by using RANGE.
This time we are inserting value so select VALUE property.
Sub Macro1() Range("A1").Select Range("A1").Value End Sub
To insert value put an equal sign and enter your value in double quotes if the value is text, if the value is numeric you can directly enter the value.
Sub Macro1() Range("A1").Select Range("A1").Value = "Hello" End Sub
Now press F8 key to run the code line by line to understand the line of codes. On the first press of the F8 key, it will highlight the macro name with yellow. Before this select B2 cell.
Now upon pressing the F8 key one more time, it should insert the value “Hello” to the cell A1.
We can also insert the value by using the Active Cell method.
The moment we select the cell it becomes an active cell. So use the property active cell to insert the value.
This is also exactly the same as the last one. Using Range object makes it “explicit” and using Active Cell makes it “Implicit”.
Example #4 – Select More than one Cell Using VBA Code
We can also select multiple cells at a time. We just need to specify the range of cells to be selected in double quotes. If you want to select cells from A1 to A5 then below is the way.
Sub Macro2() Range("A1:A5").Select End Sub
Run this code using F5 key or manually to show the result.
We can also select noncontiguous cells with a Range object. For example, if you want to select cells from A1 to A5, C1 to C5, E5 cell then you can do this like this.
Sub Macro3() Range("A1:A5,C1:C5,E5").Select End Sub
Run this code manually or through F5 key to show the result.
One thing here is we need to start the double quote before we specify any cell then close after the last cell.
Not only cells we can also select the named ranges as well by using the name of the range.
Example #5 – Select cells by using CELLS Property in VBA
Not through RANGE object but also through CELLS property we can select the cell – CELLS is property in VBA.
In CELLS property we need to specify the row number and column number we are selecting. This is unlike a range method where we used A1, A5, C5, C10 like references.
For example, CELLS (1,1) means A1 cell, CELLS (2,5) means E2 cell. Like this, we can select the cells.
Sub Macro4() Cells(2, 3).Select End Sub
You can download this VBA Select Cell Template – VBA Select Cell Excel Template
This has been a guide to VBA Select Cell. Here we learn how to select a cell in excel using VBA Code along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –