VBA Range Object | How to use Range in VBA Excel? (Examples)

Excel VBA Range Object

Range is a property in VBA is similar to the worksheet property, range property also has many applications and uses, when we write our code and specify a particular cell range or a particular cell it is done by the range property method, it is used to give reference to cells rows and columns.

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 Range Objects finds its usefulness.

How to use the Range Object?

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

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 & methods.
Property: Here, the information about the object is stored.
Method: It refers to the action that the object will perform.

For example, for 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 (.)

Application.Workbooks.Worksheets.Range

syntax

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

Examples

You can download this VBA Range Excel Template here – VBA Range Excel Template

Example #1 – Referring to a Single 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 excel 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.

Popular Course in this category

VBA Training (3 Courses, 12+ Projects)4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion

View Course

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 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.

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.

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.

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

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

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 of cells in the worksheet and merge them into one cell.

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

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 maybe, 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.

Things to Remember

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, Range follows object hierarchy patter using the “.” Notation.

VBA Range