VBA Selection Range

After the basic stuff with VBA, it is important to understand how to work with a range of cells in the worksheet. Once you start executing the codes practically, you need to work with various cells. So, it is important to understand how to work with various cells. One such concept is VBA's "Selection of Range." This article will show you how to work with the "Selection Range" in Excel VBA.

Selection and Range are two different topics, but when we say to select the range or selection of range, it is a single concept. RANGE is an object, "Selection" is a property, and "Select" is a method. People tend to be confused about these terms. It is important to know the differences in general.

Example #1

Assume you want to select cell A1 in the worksheet, then. But, first, we need to specify the cell address by using a RANGE object like below.

Code:

After mentioning the cell, we need to select and put a dot to see the IntelliSense list, which is associated with the RANGE object.

From this variety of lists, choose the "Select" method.

Code:

Sub Range_Example1()

  Range("A1").Select

End Sub

Now, this code will select cell A1 in the active worksheet.

To select the cell in the different worksheets, specify the worksheet by its name. To specify the worksheet, we need to use the "WORKSHEET" object and enter the worksheet name in double quotes.

For example, if you want to select cell A1 in the worksheet "Data Sheet," specify the worksheet just like below.

Code:

Sub Range_Example1()

  Worksheets ("Data Sheet")

End Sub

Then continue the code to specify what we need to do in this sheet. For example, in "Data Sheet," we need to select cell A1 so that the code will be RANGE("A1").Select.

Code:

Sub Range_Example1()

  Worksheets("Data Sheet").Range("A1").Select

End Sub

When you try to execute this code, we will get the below error.

It is because "we cannot directly supply a range object and select method to the worksheets object."

First, we need to select or activate the VBA worksheet, and then we can do whatever we want.

Code:

Sub Range_Example1()

  Worksheets("Data Sheet").Activate
  Range("A1").Select

End Sub

It will now select cell A1 in the worksheet "Data Sheet."

Example #2 - Working with Current Selected Range

Selecting is different, and working with an already selected range of cells is different. For example, assume you want to insert a value "Hello VBA" to cell A1 then we can do it in two ways.

Firstly we can directly pass the VBA code as RANGE(“A1”).Value = “Hello, VBA.”

Code:

Sub Range_Example1()

  Range("A1").Value = "Hello VBA"

End Sub

This code will insert the value "Hello VBA" to cell A1, irrespective of which cell is currently selected.

Look at the above result of the code. When we execute this code, it has inserted the value "Hello VBA," even though the currently selected cell is B2.

Secondly, we can insert the value into the cell using the "Selection" property. But, first, we need to select the cell manually and execute the code.

Code:

Sub Range_Example1()

  Selection.Value = "Hello VBA"

End Sub

What this code will do is insert the value "Hello VBA" to the currently selected cell. For example, look at the below example of execution.

When we executed the code, my current selected cell was B2. Therefore, our code inserted the same value to the currently selected cell.

Now, we will select cell B3 and execute. There also, we will get the same value.

Another thing we can do with the "selection" property is insert a value to more than one cell. So, for example, we will select the range of cells from A1 to B5 now.

If we execute the code for all the selected cells, we get the value "Hello VBA."

So, the simple difference between specifying a cell address by RANGE object and Selection property is that the Range object code will insert value to the cells specified explicitly.

But in the Selection object, it does not matter which cell you are in. It will insert the mentioned value to all the selected cells.

• We cannot directly supply the select method under the Selection property.
• The RANGE is an object, and selection is property.
• Instead of range, we can use the CELLS property.