Selection Range in Excel VBA
After the basis stuffs 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 most of the time you need to work with a range of cells. So, it is important to understand how to work with a range of cells and one of such concept is VBA “Selection of Range”. In this article, we 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 confuse with these terms, it is important to know the differences in general.
How to Select a Range in Excel VBA?
For an example assume you want to select the cell A1 in the worksheet then first we need to specify the cell address by using RANGE object like the below.
After mentioning the cell that we need to select put dot to see the IntelliSense list which is associated with RANGE object.
Form this variety of list chooses “Select” method.
Sub Range_Example1() Range("A1").Select End Sub
Now, this code will select the cell A1 in the active worksheet.
If you want to select the cell in the different worksheet then first we need to 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 the cell A1 in the worksheet “Data Sheet” then first specify the worksheet just like below.
Sub Range_Example1() Worksheets ("Data Sheet") End Sub
Then continue the code to specify what we need to do in this sheet. In “Data Sheet” we need to select the cell A1, so the code will be RANGE(“A1”).Select.
4.6 (247 ratings)
Sub Range_Example1() Worksheets("Data Sheet").Range("A1").Select End Sub
When you try to execute this code we will get below error.
The reason for this is “we cannot directly supply range object and select method to the worksheets object”.
First, we need to select or activate the VBA worksheet then we can do whatever the things we wanted to do.
Sub Range_Example1() Worksheets("Data Sheet").Activate Range("A1").Select End Sub
Now this will select the cell A1 in the worksheet “Data Sheet”.
Working with Current Selected Range
Select is a different thing and working with an already selected range of cells is different. Assume you want to insert a value “Hello VBA” to the cell A1 then we can do it in two ways.
Firstly we can directly pass the VBA code as RANGE(“A1”).Value = “Hello VBA”.
Sub Range_Example1() Range("A1").Value = "Hello VBA" End Sub
What this code will do is this will just insert the value “Hello VBA” to the cell A1 irrespective of which cell is selected currently.
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 to the cell by using “Selection” property. For this first, we need to select the cell manually and execute the code.
Sub Range_Example1() Selection.Value = "Hello VBA" End Sub
What this code will do is this will insert the value “Hello VBA” to the currently selected cell. For an example look at the below example of execution.
When I executed the code my current selected cell was B2 and our code inserted the same value to the currently selected cell.
Now I will select the cell B3 and execute, there also we will get the same value.
One more thing we can do with “selection” property is we can insert value to more than one cell as well. For example, I will select the range of cells from A1 to B5 now.
Now if I execute the code, for all the selected cells we will get the value as “Hello VBA”.
So, the simple difference between specifying cell address by RANGE object and Selection property is, in Range object code will insert value to the cells which are specified explicitly.
But in Selection object it doesn’t matter which cell you are in, it will insert the mentioned value to all the selected cells.
Things to Remember Here
- We cannot directly supply the select method under Selection property.
- RANGE is an object and selection is property.
- Instead of range, we can use CELLS property.
This has been a guide to VBA Selection Range. Here we learn how to select a range in excel VBA along with examples and download excel template. Below are some useful excel articles related to VBA –