Home > Articles > Home & Office Computing > Microsoft Applications

Excel 2016 VBA and Macros: Referring to Ranges

This chapter from Excel 2016 VBA and Macros shows you different ways of referring to ranges, such as specifying a row or column. You’ll also find out how to manipulate cells based on the active cell and how to create a new range from overlapping ranges.
This chapter is from the book

A range can be a cell, a row, a column, or a grouping of any of these. The RANGE object is probably the most frequently used object in Excel VBA; after all, you are manipulating data on a sheet. Although a range can refer to any grouping of cells on a sheet, it can refer to only one sheet at a time. If you want to refer to ranges on multiple sheets, you must refer to each sheet separately.

This chapter shows you different ways of referring to ranges, such as specifying a row or column. You’ll also find out how to manipulate cells based on the active cell and how to create a new range from overlapping ranges.

The Range Object

The following is the Excel object hierarchy:

Application > Workbook > Worksheet > Range

The Range object is a property of the Worksheet object. This means it requires that a sheet be active or else it must reference a worksheet. Both of the following lines mean the same thing if Worksheets(1) is the active sheet:

Range("A1")
Worksheets(1).Range("A1")

There are several ways to refer to a Range object. Range("A1") is the most identifiable because that is how the macro recorder refers to it. However, all the following are equivalent when referring to a range:

Range("D5")
[D5]
Range("B3").Range("C3")
Cells(5,4)
Range("A1").Offset(4,3)
Range("MyRange") 'assuming that D5 has a 'Name of MyRange

Which format you use depends on your needs. Keep reading....It will all make sense soon!

  • + Share This
  • 🔖 Save To Your Account