Excel VBA Range Object

Susan GipsonBySusan Gipson Hours Updated

What is VBA Range?

The VBA Range Object represents a cell or multiple cells in your Excel worksheet. It is the most important object of Excel VBA. By using Excel VBA range object, you can refer to,

  • A single cell
  • A row or a column of cells
  • A selection of cells
  • A 3-D range

As we discussed in our previous tutorial, that VBA is used to record and run Macro. But how VBA identify what data from the sheet needs to be executed. This is where VBA Range Objects is useful.

In this tutorial, you will learn-

Introduction to Referencing Objects in VBA

Referencing Excel’s VBA Range Object and the Object Qualifier.

  • Object Qualifier: This is used for referencing the object. It specifies the workbook or worksheet you are referring to.

To manipulate these cell values, Properties and Methods are used.

  • Property: A property stores information about the object.
  • Method: A method is an action of the object it will perform. Range object can perform actions like selected, copied, cleared, sorted, etc.

VBA follow object hierarchy pattern to refer object in Excel. You have to follow the following structure. Remember the .dot overhere connects the object at each of the different levels.

Application.Workbooks.Worksheets.Range

There are two main types of default objects.

How to refer to Excel VBA Range Object using Range property

Range property can be applied in two different types of objects.

  • Worksheet Objects
  • Range Objects

Syntax for Range Property

  1. The keyword “Range.”
  2. Parentheses that follow the keyword
  3. Relevant Cell Range
  4. Quotation (” “)
Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1")

When you refer Range object, as shown above, it is referred as fully qualified reference. You have told Excel exactly which range you want, what sheet and in what worksheet.

Example: MsgBox Worksheet(“sheet1”).Range(“A1”).Value

Using Range property, you can perform many tasks like,

  • Refer to a Single cell using range property
  • Refer to a Single cell using the Worksheet.Range Property
  • Refer to an entire row or column
  • Refer to merged cells using Worksheet.Range Property and many more

As such it will be too lengthy to cover all scenarios for range property. For scenarios mentioned above, we will demonstrate an example only for one. Refer to a Single cell using range property.

Refer to a Single cell using the Worksheet.Range Property

To refer to a single cell, you have to refer to a single cell.

Syntax is simple “Range(“Cell”)”.

Here, we will use “.Select” command to select the single cell from the sheet.

Step 1) In this step, open your excel.

VBA Range Objects

Step 2) In this step,

  • Click on VBA Range Objects button.
  • It will open a window.
  • Enter your program name here and click ‘OK’ button.
  • It will take you to main Excel file, from top menu click on ‘stop’ record button to stop recording Macro.

VBA Range Objects

Step 3) In next step,

  • Click on Macro button VBA Range Objects from the top menu. It will open the window below.
  • In this window, Click on the ‘edit’ button.

VBA Range Objects

Step 4) The above step will open VBA code editor for file name “Single Cell Range”. Enter the code as shown below for selecting range “A1” from the excel.

VBA Range Objects

Step 5) Now save the file VBA Range Objects and run the program as shown below.

VBA Range Objects

Step 6) You will see Cell “A1” is selected after execution of the program.

VBA Range Objects

Likewise, you can select a cell with a particular Name. For example, if you want to search cell with name “Guru99- VBA Tutorial”. You have to run the command as shown below. It will select the cell with that name.

Range(“Guru99- VBA Tutorial”).Select

To apply other range object here is the code sample.

Range for selecting cell in Excel Range declared
For single Row Range(“1:1”)
For single Column Range(“A: A”)
For Contiguous Cells Range(“A1:C5”)
For Non-Contiguous Cells Range(“A1:C5, F1:F5”)
For Intersection of two ranges Range(“A1:C5 F1:F5”)

(For intersection cell, remember there is no comma operator)

To merge Cell Range(“A1:C5”)

(To merge cell use “merge” command)

Cell Property

Similarly to the range, in VBA you can also you “Cell Property”. The only difference is that it has an “item” property that you use to reference the cells on your spreadsheet. Cell property is useful in a programming loop.

For example,

Cells.item(Row, Column). Both the lines below refer to cell A1.

  • Cells.item(1,1) OR
  • Cells.item(1,”A”)

Range Offset property

Range offset property will select rows/columns away from its original position. On the basis of the range declared, cells are selected. See example below.

For example,

Range("A1").offset(Rowoffset:=1, Columnoffset:=1).Select

The result for this will cell B2. The offset property will move A1 cell to 1 column and 1 row away. You can change the value of rowoffset / columnoffset as per requirement. You can use a negative value (-1) to move cells backward.

Download Excel containing above code

Download the above Excel Code

Summary

  • The VBA Range Object represents a cell or multiple cells in your Excel worksheet
  • A single cell
  • A row or a column of cells
  • A selection of cells
  • A 3-D range
  • To manipulate cell values, Properties and Methods are used
  • A property stores information about the object
  • A method is an action of the object it will perform like select, merge, sorted, etc.
  • VBA follow object hierarchy pattern to refer object in Excel using .dot operator
  • Range property can be applied in two different types of objects
  • Worksheet Objects
  • Range Objects

You Might Like: