Home > Articles

Referring to Ranges

This chapter is from the book

This chapter is from the book

A range can be a cell, row, column, or a grouping of any of these. The Range object is probably the most frequently used object in Excel VBA—after all, you're manipulating data on a sheet. But 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 have to 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 learn how to manipulate cells based on the active cell and how to create a new range from overlapping ranges.

In this chapter

  • The Range Object
  • Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range
  • Referencing Ranges in Other Sheets
  • Referencing a Range Relative to Another Range
  • Using the Cells Property to Select a Range
  • Using the Offset Property to Refer to a Range
  • Using the Resize Property to Change the Size of a Range
  • Using the Columns and Rows Properties to Specify a Range
  • Using the Union Method to Join Multiple Ranges
  • Using the Intersect Method to Create a New Range from Overlapping Ranges
  • Using the IsEmpty Function to Check Whether a Cell Is Empty
  • Using the CurrentRegion Property to Quickly Select a Data Range
  • Case Study
  • Using the Areas Collection to Return a Non-contiguous Range
  • Next Steps

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 either a sheet be active or 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 of referring to a Range object; Range("A1") is the most identifiable because that is how the macro recorder does it. But each of the following is equivalent:

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

Discussions

comments powered by Disqus