VBA Selecting Range

Excel VBA Selecting Range

To select any kind of range in VBA we have a function called SELECT. It doesn’t mean that what type of cells we want to select in the Excel worksheet. But, If we want to use the range of cells or a combination of cells in VBA, then VBA Select is the function that would help us in selecting the range of cells we want. In actual, SELECT is an application in VBA which is used by applying it after we choose the range of cells to be select. Choosing the cells we want to select doesn’t mean that we are actually selecting it. We need to place SELECT after we fix the cells which we want to consider in our range. The good thing about Select is, it does not have any syntax to be followed.

How to Select a Range of Cells in Excel VBA?

We will learn how to select a range of cells in Excel by using the VBA Code. For this, follow the below steps:

Example #1

To apply the SELECT application using VBA,

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 2: Write the subprocedure of VBA Selecting Range in the same name or in the different names which defines the operation we do.

Code:

Sub VBA_Range()

End Sub

Step 3: Now suppose, if we want to select the range of cells from cell A1 to B10 then use RANGE and in inverted quotes put the cells under range.

Code:

Sub VBA_Range()

Range("A1:B10")

End Sub

Step 4: Now to select the required application, after a dot and select the application called SELECT as shown below.

Code:

Sub VBA_Range()

Range("A1:B10").Select

End Sub

Step 5: Now we will compile the code by pressing function key F8 and to run the code, click on the Play button located below the menu bar. We will see in the current worksheet, cells from A1 to B10 are selected or covered in the highlighted area.

Example #2

There is another way to select any random cell range we want. For this, we will use the same coding pattern that we saw in example-2. For this, follow the below steps:

Step 1: Write the subprocedure for VBA Selecting Range.

Code:

Sub VBA_Range2()

End Sub

Step 2: Now in the Range function, we will put the random cells that we want to select in place of sequential range. Let’s put cell A1, B2 and C3 separated by commas into the brackets.

Code:

Sub VBA_Range2()

Range("A1, B2, C3").Select

End Sub

Step 3: Now if the run the code, we will see cell A1, B2 and C3 will now be selected with a highlighted portion as shown below.

Example #3

There is one more method to select the Range. Here we will be using define a variable for RANGE function first. For this, follow the below steps:

Step 1: Write the subprocedure for VBA Selection Range. And in that define a variable for RANGE.

Code:

Sub VBA_Range3()

Dim SelectRNG As Range

End Sub

Step 2: Then use SET with defined variable and use RANGE function along with the cells which we want to select.

Code:

Sub VBA_Range3()

Dim SelectRNG As Range
Set SelectRNG = Range("A1:B10")

End Sub

Step 3: At last, use the SELECT application with a defined variable as shown below.

Code:

Sub VBA_Range3()

Dim SelectRNG As Range
Set SelectRNG = Range("A1:B10")
SelectRNG.Select

End Sub

Step 4: Now again compile the code if required and run. We will see the range of cells A1 to B10 will get selected with the highlighted region.

Example #4

There is also another simplest way to select the range. For this, follow the below steps:

Step 1: For this again open a module and write the subprocedure for VBA Selecting Range. Now we will be using the function call CELLS. CELLS function in VBA allows us to choose the cells we want to select.

Code:

Sub VBA_Range4()

End Sub

Step 2: Now put the cell numbers in the vertex of the X and Y positions. If we want to select the cell B3 then X would be 3 and Y would be 2 as shown below.

Code:

Sub VBA_Range4()

Cells(

End Sub

Step 3: Now use the SELECT application after the CELLS as shown below.

Code:

Sub VBA_Range4()

Cells(3, 2).Select

End Sub

Step 4: Now if we run the code, we will see, the cursor will be now placed to cell B3 as shown below.

Pros of VBA Selecting Range

• Shown examples have different but easiest ways to select the range.
• Selecting the range using directly RANGE function with SELECT is the best one-line code.
• We can select cells as a range in continuously or randomly.

Things to Remember

• To have a proper visual of range getting selected, put the cursor away from the range we want to select.
• If we choose the randomly distributed cells as Range, then some operation becomes limited to it. Such as, we cannot copy or cut the data in such a pattern manually.
• Processes shown for selecting the range in all the above examples are not limited to these. We can try the combination of all the different examples simultaneously we want.
• Once coding is done, save the Excel file in Macro Enabled Excel format which would help in retaining and saving the code multiple times.

Recommended Articles

This is a guide to the VBA Selecting Range. Here we discuss how to Select a Range of Cells in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Input
2. VBA Login
3. VBA Month
4. VBA Login