Select Range in VBA
The word “Select” doesn’t need any special introduction it simply means select the mentioned range of cells in the code. “Select” is a method in VBA, so if you want to select a range of excel cells from A1 to A10 then we need to mention the range of cells addresses by using VBA RANGE object.
So, there are different ways of approaching the range selection method in VBA, in this article we will show you how to go about business with this.
Examples of Excel VBA Select Range
For example, look at the below image of the data.
Copy the above data to your worksheet. Now we will see how to select the range of cells using various VBA examples.
Now we will see how to select the range of cells from A2 to A12.
Open Visual Basic Editor in Excel by pressing ALT + F11, now we will see the below window in VBA.
Click on the INSERT tab and choose “Module”.
Now we will see the new MODULE. Start the SUB procedure by naming the macro.
Sub Select_Example1() End Sub
Inside this VBA subprocedure, we will start writing the code. Since our task is to select the range of cells from A2 to A12 open VBA RANGE object to select the range.
As you can see the syntax of range object it says “Cell1” i.e. what the select we are addressing is. Since we are selecting the range of cells from A2 to A12 mention these cell addresses in double-quotes.
Sub Select_Example1() Range ("A2:A12") End Sub
Now put a dot (.) to see the intellisense list of properties and methods associated with this range object cells.
From the intellisense list choose the “Select” method. Now our code reads like this RANGE (“A2:A12”).Select
Sub Select_Example1() Range("A2:A12").Select End Sub
Now execute the code and see the magic of the code.
Example #2 – Using Object Variables
We can select the range of cells by using VBA object variables as well.
First, define the VBA variable as Range.
Sub Select_Example2() Dim Rng As Range End Sub
The reason why we have declared the VBA variable data type as “Range” because since we are referring to the range cells we have assigned the data types as “Range”.
Since the variable is RANGE object variable we need to use the word “Set” key to assign the range of cells to reference to this variable.
Sub Select_Example2() Dim Rng As Range Set Rng = Range("A2:A12") End Sub
Now using the variable name “Rng” we can do all the properties and methods. Now “Rng” variable refers to the range of cells from A2 to A12, so now no longer need to use the code RANGE (“A2:A12”) rather we just need to use the variable name “Rng”.
Mention the “Rng” variable and put a dot to see the IntelliSense list. Then choose the “Select” method.
Sub Select_Example2() Dim Rng As Range Set Rng = Range("A2:A12") Rng.Select End Sub
This vba code too selects the range of cells from A2 to A12.
Example #3 – Using Cells Property
If we want to select any single cell we can use the vba “CELLS” property also.
Open CELLS property.
When you use CELLS property at a given point in time we can choose only one cell.
For example, if we want to choose the cell A2 we need to specify the row number and column number. Since we need to select the A2 cell, the row number is 2 and the column is 1.
Sub Select_Example3() Cells(2,1) End Sub
Put dot (.) and choose the “Select” method.
Sub Select_Example3() Cells(2, 1).Select End Sub
Note: When you use CELLS property we don’t get to see the intellisense list, so we need to be absolutely sure about code spellings.
Run the code and it will select the A2 cell.
Like this using multiple methods, we can select the range of cells in excel vba.
Things to Remember
- “Select” is a method in VBA.
- Using VBA RANGE object we can select multiple ranges of cells but using CELLS property we can choose only one cell at a time.
- Range Object can show the intellisense list but CELLS property doesn’t show the intellisense list, so you should be sure of what you are writing.
This has been a guide to VBA Select Range. Here we discuss how to select a range of cells in excel using vba code with the help of examples – 1) Select Method 2) Object Variable 3) Cells Property. You can learn more from the following VBA articles –
- ByVal Argument in Excel VBA
- VBA Randomize | RND Function
- VBA Month Function
- VBA Object Required
- VBA CreateObject Function
- Named Range in VBA
- UsedRange in VBA
- Subscript Out of Range in VBA
- Name Worksheet in VBA