Referring to ranges
- By Bill Jelen and Tracy Syrstad
- 3/11/2022
- The Range object
- 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 select a data range
- Using the Areas collection to return a noncontiguous range
- Referencing tables
- Next steps
Using the Columns and Rows properties to specify a range
The Columns and Rows properties refer to the columns and rows of a specified Range object, which can be a worksheet or a range of cells. They return a Range object referencing the rows or columns of the specified object.
You’ve seen the following line used, but what is it doing?
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
This line of code finds the last row in a sheet in which column A has a value and places the row number of that Range object into the variable called FinalRow. This can be useful when you need to loop through a sheet row by row; you will know exactly how many rows you need to go through.
Note
Some properties of columns and rows require contiguous rows and columns in order to work properly. For example, if you were to use the following line of code, 9 would be the answer because only the first range would be evaluated:
Range("A1:B9, C10:D19").Rows.Count
However, if the ranges were grouped separately, the answer would be 19. Excel takes the top-left cell address, A1, and the bottom-right cell address, D19, and counts the rows in the range A1:D19:
Range("A1:B9", "C10:D19").Rows.Count