VBA Selection Range

Excel VBA Selection Range

We all might have seen the process where we need to select the range so that we could perform some work on it. This is the basic step towards any task we do in Excel. If we do any manual work, then we can select the range of cells manually. But, while automating any process or work it is necessary to automate the process of Selection of Range as well. And VBA Selection Range is the basic steps toward any VBA code. When we write the steps for Selection of Range, their Range becomes the Object and Selection becomes the property. Which means the cells which we want to select are Objects and selection process of the property in VBA Selection Range.

How to Select a Range in Excel VBA?

We will learn how to select a range in Excel by using the VBA Code.

Excel VBA Selection Range – Example #1

In the first example, we will see a very simple process where we will be selecting any range of cells using VBA code. For this, follow the below steps:

Step 1: Open a Module from the Insert menu tab where we will be writing the code for this.

Step 2: Write the subcategory of VBA Selection Range or we can choose any other name as per our choice to define it.

Code:

Sub Selection_Range1()

End Sub

Step 3: Now suppose, we want to select the cells from A1 to C3, which forms a matrix box. Then we will write Range and in the brackets, we will put the cells which we want to select.

Code:

Sub Selection_Range1()

Range("A1:C3")

End Sub

Step 4: Now we have covered the cells. Further, we can apply any function to it. We can select the cells, select the values it has or copy the selected range as well. Here we will simply select the range.

Code:

Sub Selection_Range1()

Range("A1:C3").Select

End Sub

Step 5: Now compile the code and run it by clicking on the Play button located below the menu bar. We will see the changes in the current sheet as cells from A1 to C3 are selected as shown below.

In a similar way, we can put any value to selected range cells. For this we will use Value function instead of Select.

Code:

Sub Selection_Range2()

Range("A1:C3").Value

End Sub

Now assign any value or text which we want to see in the selected range cells. Here that value is My Range.

Code:

Sub Selection_Range2()

Range("A1:C3").Value = "My Range"

End Sub

Now again run the code by clicking on the Play Button.

We will see the required text which we were in code value is got printed to the selected range.

Excel VBA Selection Range – Example #2

There is another way to implement VBA Selection Range. For this, follow the below steps:

Step 1: Write the subcategory of VBA Selection Range as shown below.

Code:

Sub Selection_Range3()

End Sub

Step 2: By this process, we can select the range of any specific sheet which we want. We don’t need to make that sheet as current. Use Worksheet function to activate the sheet which wants by putting the name or sequence of the worksheet.

Code:

Sub Selection_Range3()

Worksheets("Sheet2").Activate

End Sub

Step 3: Now again as per example-1, we will select the range of the cells which want to select. Here we are considering the same range from cell A1 to C3.

Code:

Sub Selection_Range3()

Worksheets("Sheet2").Activate
Range("A1:C3").Select

End Sub

Step 4: Now run the code by clicking on the Play Button. We will see, the cells from A1 to C3 of the worksheet which is Name as Sheet2 are now selected.

As we have already selected the cells which we wanted, so now we can again write the one line code by which we will insert any text to selected cells. Or we can select the new range of cells manually also to see the changes by this code.

Step 5: For this use Selection function along with Value and choose the value which we want to see. Here our value is the same as we used before as My Range.

Code:

Sub Selection_Range3()

Worksheets("Sheet2").Activate
Range("A1:C3").Select
Selection.Value = "My Range"

End Sub

Step 6: Now again run the code by clicking on Play Button.

We will see, the selected cells from A1 to C3 got the value as My Range and those cells are still selected.

Excel VBA Selection Range – Example #3

In this example, we will see how to move the cursor from a current cell to the far most end cell. This process of selecting the end cell of the table or blank worksheet is quite useful in changing the location from where we can select the range. In Excel, this process is done manually by Ctrl + any Arrow key. Follow the below steps to use VBA Selection Range.

Step 1: Write the subcategory of VBA Selection Range again.

Code:

Sub Selection_Range4()

End Sub

Step 2: Choose the reference range cell from where we want to move the cursor. Let’s say that cell is B1.

Code:

Sub Selection_Range4()

Range("B1")

End Sub

Step 3: Now to move to the End table or sheet towards right we will use xlToRight and for left it would be changed to xlToLeft as shown below.

Code:

Sub Selection_Range4()

Range("B1").End(xlToRight).Select

End Sub

Step 4: Now run the code by pressing F5 key.

We will see, our cursor from anywhere from the first row or cell B1 will move to the far end to the sheet.

In a similar way, we can move the cursor and select the cell of the far down or up location of any sheet by xlDown or xlUP. Below is the code for selecting the far down cell of a sheet from reference cell B1.

Code:

Sub Selection_Range4()

Range("B1").End(xlDown).Select

End Sub

Pros of Excel VBA Selection Range

• This is as easy as selecting the range of cells manually in Excel.
• We can choose any type of range which we cannot do manually.
• We can select and fill the cells which are only possible in excel by Find and Replace option.
• Selecting the range cells and putting the data into that can be done simultaneously with one line of code.

Things to Remember

• Using xlDown/Up and xlToLeft/Right command in code will take us to cells which is a far end or to the cell which has data. Means, the cell with the data will stop and prevent us from taking to the far end of the sheet.
• We can choose any type of range but, make sure the range of cells is in sequence.
• Random selection of cell is not allowed with these shown examples.
• Always save the excel file as Macro Enable excel to prevent losing the code.

Recommended Articles

This is a guide to VBA Selection Range. Here we discuss how to select a range in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Conditional Formatting
2. Find and Replace in Excel
3. VBA Remove Duplicates
4. Excel Named Range