What Is VBA Range In Excel?
VBA RANGE is used to access a range of cells and is the most important object in Excel VBA. The range could be a single cell, multiple cells, or an entire row or column.
By specifying the range of cells using VBA Range, we can access all the other properties and methods associated with the Range object. The Range object is a part of Excel’s object hierarchy, as shown below.
Excel Application >>> Workbooks >>> Worksheets >>> Range
By referring to the specific range of cells, we can
- Select the range
- Insert value to the referencing range
- Read values from the referencing range
- Delete values
- Change the formatting
We can refer to cells or a range of cells using –
- CELLS object
- RANGE object
- OFFSET object
- VBA RANGE is an object used to reference specific cells in Excel VBA.
- The syntax of the VBA RANGE object is Range(Cell1,[Cell2]) As Range where Cell 1 is the mandatory argument and Cell 2 is the optional argument.
- It is mandatory to mention the first cell or range of cells in double-quotes. Likewise, we can use double-quotes for the second cell or range also.
- Remember to define a variable and set the reference range for that variable.
The syntax of the VBA RANGE object is
[Cell 1]: It is a mandatory argument. We need to mention the first cell or range of cells in double quotes.
[Cell 2]: It is an optional argument. We can mention the second range of cells in double quotes.
For example, if we want to refer to cells in the range A2:A5, we can enter the code like the following.
Likewise, if we want to refer to multiple ranges of cells like A2:A5 and C2:C5, then we can give the cell address like the following.
How To Use The VBA Range Function In Excel?
Let us look at the basic example of using the RANGE object in VBA coding. Assume we need to select cell B2 in the worksheet named “Sales”.
Following are the steps to be followed to write our own VBA code from scratch.
Step 1: Open the Excel workbook and create a new sheet called Sales with other sheets in place.
Step 2: Go to the Developer tab and click on Visual Basic.
Note: You can press the excel shortcut keys ALT + F11 instead.
Step 3: The Visual Basic Editor window opens, as shown in the following image.
Step 4: Go to the Insert tab and click on Module.
Step 5: This will insert the module. Next, we will see the white coding window on the right side.
Step 6: Start the sub-procedure by giving a name to the macro.
Step 7: Enter the object name RANGE, and we will see the IntelliSense list showing the possible matches.
Step 8: By selecting the VBA RANGE object, open parenthesis and enter the targeted cell B2 in double quotes.
Step 9: Once the cell reference in excel is given in double quotes, close the parenthesis and enter the dot to see all the properties and methods associated with the RANGE object.
Step 10: Based on the requirement, we can choose the required property or method. For example, if we want to select a cell, then we can choose the Select method.
Now, when we run the code by pressing the shortcut key F5, this code will select cell B5 in the active worksheet.
Example #1 – Select Multiple Ranges
Assume we need to insert the value Hello to the range of cells A2 to B5; then, we can reference this range of cells by entering the start and end cells with a colon (:) in between.
Once the range is given, enter a dot (.) and select the VALUE property because, with the given range of cells, we are inserting a value.
To insert a value to the given cells, first, we need to enter an equal sign and then give the value to be entered in cells in double quotes.
Following is the code for your reference.
Range(“A2:B5”).Value = “Hello”
Place a cursor inside the code and hit the Run button.
Note: You can press the shortcut key F5 to run the code.
When we run the code, we will get the value Hello in cell range A2:B5.
Example #2 – Select Non-contiguous Cells
In the previous example, we selected the range of cells with a continuous range. However, we can also select multiple ranges of cells. For example, if we want to select the range of cells A2:B5 and D2:E5, then we can use the following code.
This will select the ranges A2:B5 and D2:E5.
In the current selection, there is an empty column i.e., Column C.
Example #3 – Select An Entire Row And Column
We can use the following code to select the entire 3rd row.
This will select the entire 3rd row.
Use the following code to select the entire column B.
This will select the entire column B when we run the code.
Example #4 – Select Range Of Particular Worksheet
We will have multiple worksheets, if we want to select a range in any particular worksheet, then we need to specify the worksheet by its name before we mention the range.
For example, if we want to select the range A2:B5 in a worksheet Sales, then we can write the code like the following.
Just recollect the object hierarchy we discussed earlier.
Excel Application >>> Workbooks >>> Worksheets >>> Range
First, we have given the worksheet object by its name, Sales, using the WORKSHEETS object. After giving the worksheet name, we used the VBA RANGE object to specify the targeted range of cells i.e., A2:B5.
Run this code, and it will select the range of cells A2:B5 in the worksheet “Sales”.
However, this code works only when we are in the worksheet “Sales”. For example, if we are in any of the other worksheets, this code will throw the following VBA Run time 1004 error.
The error says Select method of Range class failed.
This is because we specifically mentioned selecting the range of cells A2:B5 in the worksheet Sales. Since we are in different worksheets, we cannot select the different worksheet ranges.
To overcome this issue, before we select the range of cells, we need to make sure that we are activating the required worksheet.
We have added the code Worksheets(“Sales”).Activate before the range selection code. It doesn’t matter which worksheet we are in.
First, it will activate the worksheet Sales and then select the range A2:B5.
Example #5 – Use Of Variables
To code more efficiently, we need to make use of variables. Assume we need to use the range A2:B5 again and again, it is not a wise way of mentioning the cells by using the RANGE object. The best way is to use variables.
Dim Rng As Range
Set Rng = Worksheets(“Sales”).Range(“A2:B5”).Select Rng.Select
Line #1 – Dim Rng As Range
First, we have defined the variable “Rng” with the Range object.
Line #2 – Set Rng = Worksheets(“Sales”).Range(“A2:B5”).Select
Next, we have set the range reference to the variable. Now the variable Rng refers to the range of cells A2:B5 in the worksheet “Sales”.
Line #3 – Rng.Select
Now, if we want to do anything with the range A2:B5, we can use the Rng variable. By using the Rng variable, we are selecting the range.
Example #6 – Select Range Dynamically
For example, look at the following data.
If we want to select the entire data table range starting from cell A1, what we do is first activate cell A1.
Then we will use the shortcut keys Ctrl + Shift + Down Arrow to select all the filled rows in column A.
Again, hold the Ctrl + Shift and press the right arrow to select all the columns on the right side.
To perform the same activity in VBA coding, we can use the RANGE object like the following.
The above code will select all the filled rows and columns starting from cell A1.
Example #7 – Copy Data From One Range To Another
We can use the RANGE property to copy the data from one range to another range.
For example, look at the following data in Excel.
If we want to copy the data in the range A1:E5 to G1:K5, then we can write the code like the following.
First, we have mentioned the copy range by using the VBA RANGE object i.e.,
Next, we are selecting the pasting range as cell G1 and used the Paste Special method.
Example #8 – Current Range
We can use the current region property to select the data table. For example, look at the following data.
A blank cell in the row or column makes the end of the current region. In Excel, the shortcut key to select the current region from the active cell is Ctrl + Shift + *.
The following code will select the current region data starting from cell A1.
This will select the range of cells from cell A1 i.e., A1:E5.
If we look at the above image, it has excluded column G, where we have the data. The reason for this though we have a blank column in between these two data columns i.e., Column F.
Important Things To Note
- RANGE is an object, not a function.
- We need to give cell references in double quotes.
- If we do not mention the worksheet name, it will select the range of cells in the active sheet only.
- We will get an error if we want to select the range of cells of different worksheets from an active worksheet.
- VALUE is a property, and SELECT is a method.
- By entering the RANGE object, we can access all the properties and methods associated with the RANGE object.
Frequently Asked Questions
To select any range of cells, we need to use the RANGE object along with the required property or method.
For example, if we want to select the range A1:B5 first, we need to provide the cell address in double quotes and then choose the selected method.
Sub Range_Select ()
We can copy a VBA range by mentioning the copying range inside the VBA RANGE object and then choosing the COPY method.
For example, if we want to copy the range A2:B5, we can use the following code.
Sub Range_Copy ()
If we use a variable that doesn’t exist in the code, we will get the subscript out-of-range error.
To have a dynamic range, we need to find the last used row and column in the worksheet and assign them to the variable; then, we use the variables to refer to the range dynamically.
This article must be helpful to understand the VBA Range, with its formula and examples. You can download the template here to use it instantly.
This has been a guide to VBA Range. Here we explain how to use VBA range object in excel with examples and downloadable excel template. You may learn more from the following articles –
Leave a Reply