VBA Range Objects | How to Use Ranges in Excel VBA (with Examples)

Excel VBA Range Object

Excel VBA Range is one of the objects used by VBA. As you Know, VBA is used to record & run macros and automate the excel tasks and do repetitive tasks faster & accurately.

In the context of the excel worksheet, the VBA range object denotes cells, either single or multiple. The range object can include a single cell, an entire row or column or multiple cells spread across rows & columns.

In order for VBA to run the macros & do the tasks, it needs to identify the cells on which the called tasks need to be performed. It is here, the concept of Excel VBA Range Objects finds its usefulness.

How to use VBA Range Object?

To refer the objects in VBA, we use the hierarchical technique. There is 3 hierarchy:

a) Object Qualifier: It refers to the location of the object, like where it is, i.e. the workbook or worksheet being referred to.

The other 2 are used to do the manipulation of cell values. These are property & method.

b) Property: Here, the information about the object is stored.

c) Method: It refers to the action that the object will perform.

For example, for VBA Range, the method will be actions like sorting, formatting, selecting, clearing etc.

This is the structure that is being followed whenever a VBA object is referred. These 3 are separated by a dot (.)

The example is below:

Application.Workbooks.Worksheets.Range

The syntax for VBA Range Property

The keyword “Range.”
Parentheses following the keyword
Relevant Cell Range
Quotation (” “)

Application.Workbooks(“Booknew.xlsm”).Worksheets(“Sheet3”).Range(“B1”)

VBA Range Object Examples

Now, we will go through some of the examples using range along with their explanations.

Excel VBA Range Example #1 – Referring to a Single Cell

The syntax is: Range(“Cell”)

Let’s suppose we need to select the cell “B2” in the “sheet1” in the workbook.

Follow the below steps:

Open the excel. Please open one with the extension “.xlsm” which means “Excel Macro-enabled Workbook”. “.xlsx” types excel workbook won’t allow you to save the macros you will be writing now.
Now, once you have opened the workbook, you need to go to the VBA Editor. You can use the shortcut, “ALT+F11” to open the editor, or use the method below as shown in the screenshot:

You will see a screen similar to below:

Now, write the code as shown in the below screenshot.

Public Sub SingleCellRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“B2”).Select
End Sub

See in the excel screenshot below that currently, cell A2 is activated. After, you run the code, note where the activated cell is.

Run the code like shown in the below screenshot:

Tip: You can also use the excel shortcut key i.e F5 to run the code

You will see Cell “B2” is selected after the execution of the program.

What you are doing here is you are giving instructions to the program to go to a particular cell in a particular worksheet of a particular workbook and do the action as told, which is here to select.

Similarly, you can use the syntax to do the selection of a wide variety of cells & ranges, and also do a different kind of actions on them. Please see the examples below:

Excel VBA Range Example #2 – Selecting an Entire Row

For example, here to select the second row. Run the below-given code to select an entire row

Public Sub EntireRowRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“2:2”).Select
End Sub

Here the range(“2:2”) signifies the second row. You can return to your Excel worksheet and see the results as shown in the screenshot below.

Excel VBA Range Example #3 – Selecting an Entire Column

For example, here to select the entire column C. Run the below-given code and see the results.

Public Sub EntireRowRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“2:2”).Select
End Sub

After entering the above-given code, you will see the entire column has been selected in your excel worksheet. Refer to the screenshot below.

Here, range(“C:C”) signifies the Column C.

Similarly, you can select continuous cells, or non-contiguous cells, an intersection of cell ranges etc.

Just make the below changes to the Range part shown in the code.

Excel VBA Range Example #4 – Selecting Contiguous Cells: Range(“B2:D6”)

Excel VBA Range Example #5 – Selecting Non-contiguous Cells: Range(“B1:C5, G1:G3”)

Excel VBA Range Example #6 – Selecting Range Intersection: Range(“B1:G5 G1:G3”)

[Note the absence of comma here]. Here you will see G1 to G3 getting selected which are the common cells in the provided range.

Now, the next example will be to select a group if cells in the worksheet and merge them into one cell.

Excel VBA Range Example #7 – Merge a Range of Cells

Suppose, you want to merge cells “B1:C5” into one. See the below-given code and follow along.

Here “.merge” is the action we are performing on the group of cells given in a range

Excel VBA Range Example #8 – Clearing the Formatting on Range of Cells

Suppose, cells “F2:H6” are highlighted in yellow and we want to clear that excel formatting. Another scenario may be, you want to remove all the formatting either in the entire worksheet or from a group of cells.

See the screenshots below to follow along. First, I will show you the formatted cells (F2:H6).

Please run the codes shown in the screenshot below to remove this formatting in the selected range of cells.

Syntax: ThisWorkbook.Worksheets(“Sheet1”).Range(“F2:H6”).ClearFormats

Public Sub ClearFormats()
ThisWorkbook.Worksheets(“Sheet1”).Range(“F2:H6”).ClearFormats
End Sub

You can refer to this screenshot given below:

Similarly, you can clear the contents of a range of cells, by using the action “.ClearContents”.

There are many such things you can do. Please try them to learn better.

Also, you can download the VBA Range Excel Template here:- VBA Range Excel Template

Things to Remember About Excel VBA Range

VBA Range object denotes a single cell or multiple cells.
To manipulate cell values, we need to use the properties and methods
To refer objects in excel, VBA Range follows object hierarchy patter using the “.” Notation.

VBA Range