Excel 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.
Table of contents
How to Select a Range in Excel VBA?
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.
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.
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.
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.
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 worksheetActivate The VBA WorksheetWhen working with VBA, we frequently refer to or use the properties of another sheet. For instance, if we're working on sheet 1 and need a value from cell A2 on sheet 2, we won't be able to access it unless we first activate the sheet. So, to activate a sheet in VBA we use worksheet property as Worksheets(“Sheet2”). Activate., and then we can do whatever we want.
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 codePass The VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. as RANGE(“A1”).Value = “Hello, VBA.”
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.
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.
Things to Remember Here
- 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.
This article is a guide to VBA Selection Range. Here, we learn how to select a range in Excel VBA along with examples and download an Excel template. Below are some useful Excel articles related to VBA: –