The VBA property “Range” allows you to narrow your selection to a single cell, a range of cells, a row, a column, or a region in three dimensions. The VBA range object in Excel can refer to a single cell or multiple cells across several rows and columns.
Using Visual Basic for Applications (VBA), Excel may be programmed with recorded macros that can then be played out automatically. The result is a more rapid and precise execution of the repetitious tasks. As part of executing the macros, VBA locates the cells where the actions above are to be taken. The range object in VBA is helpful for such situations. The VBA range property is valuable in various cases, much like the worksheet property.
Syntax
Range(Cell1, [Cell2]) As Range
How to use the VBA Range Function in Excel
The range object is referred to in a hierarchical fashion in Excel VBA. The following objects make up this hierarchical structure of three levels:
- Object Qualifier: It tells the location of the object. The item is stored in a workbook, also known as a worksheet.
- Property: This holds information about the object.
- Method: It describes the operation that the object will carry out. Example: Methods include sorting, formatting, selecting, cleaning, etc., performed on a range of data.
When referencing a VBA object, be sure to use the provided hierarchy. The dot operator (.) separates these three parts in the following way:
Application. Workbooks. Worksheets. Range
See More: VBA Comments
Range Property
The cells in a Worksheet can be accessed via a number of its properties. Most actions on Excel worksheets, such as “A1,” “A3:C6,” and others, share a typical argument with the range property.
The range property is helpful for two distinct classes of objects:
- Worksheet Objects
- Range Objects
Syntax
Application.Workbooks(“Book1.xlsm”).Worksheets'(“Sheet1”).Range(“A1”)
The Range property is quite versatile and is useful for various purposes. These responsibilities involve:
- A single cell by using the Range property.
- A single cell using the Worksheet. Range property.
- A complete column or row.
- Merged cells by Worksheet.Range property, etc.
| The Range for Selecting Cell | Syntax |
| 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 the intersection of two ranges | Range(“A1:C5 F1:F5”) |
| To merge cell | Range(“A1:C5”) |
Cell Property
The Cell property is quite analogous to the Range, except for its “item” property, which helps to refer to specific cells in a spreadsheet. The programming loop can benefit from cell property.
For Example
Cells.item(Row, Column)
- Cells.item(1,1) or
- Cells.item(1, “A”)
Range Offset Property
The Range offset property helps select rows or columns and shift them away from their initial location. Cells are chosen based on the Range declared.
Syntax
Range(“A1”).offset(Rowoffset:=1, columnoffset:=1).Select
