VBA Range

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

Syntax

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.

Range(“A2:A5”)

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.

Range(“A2:A5”,“C2:C5”)

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.

Examples

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.

Sub Range_Example2()

Range(“A2:B5”).Value = “Hello”

End Sub

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.

Sub Range_Example3()

Range(“A2:B5, D2:E5”).Select

End Sub

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.

Sub Range_Example4()

Range(“3:3”).Select

End Sub

This will select the entire 3rd row.

Use the following code to select the entire column B.

Sub Range_Example4()

Range(“B:B”).Select

End Sub

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.

Sub Range_Example5()

Worksheets(“Sales”).Range(“A2:B5”).Select

End Sub

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.

Sub Range_Example5()

Worksheets(“Sales”).Activate 

Worksheets(“Sales”).Range(“A2:B5”).Select

End Sub

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.

Sub Range_Example6()

Dim Rng As Range

Set Rng = Worksheets(“Sales”).Range(“A2:B5”).Select    Rng.Select

End Sub

Explanation:

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.

Sub Range_Example6()

Range(“A1”, Range(“A1”).End(xlToRight).End(xlDown)).Select

End Sub

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.

Sub Range_Example8()

Range(“A1:E5”).Copy
Range(“G1”).PasteSpecial

End Sub

First, we have mentioned the copy range by using the VBA RANGE object i.e.,

Range(“A1:E5”).Copy

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.

Sub Range_Example9()

Range(“A1”).CurrentRegion.Select

End Sub

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

Download Template

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.

Recommended Articles

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 –

• VBA Subscript Out of Range
• VBA Collection
• VBA Not Equal