Home > Articles > Home & Office Computing > Microsoft Applications

Referring to Excel Ranges

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, and in this chapter, Bill Jelen and Tracy Syrstad demonstrate different ways of referring to ranges, as well as how to manipulate cells based on the active cell and how to create a new range from overlapping ranges.

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
VBA and Macros for Microsoft Excel

This chapter is from the book

VBA and Macros for Microsoft Excel

Discussions

comments powered by Disqus