VBA Range

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

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:

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

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

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

Recommended Articles

This has been a guide to VBA Range. Here we learn how to select a particular cell and range of cells with the help of VBA range objects and examples in excel. You may also have a look at other articles related to Excel VBA –

• Excel VBA Cells Reference
• Use VLookup in VBA
• Using VBA Msg Box