- 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
- Using the Areas Collection to Return a Non-contiguous Range
- Next Steps
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using the Columns and Rows Properties to Specify a Range
Columns and Rows 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 = Range("A65536").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 FinalRow. This can be very useful when you need to loop through a sheet row by row—you'll know exactly how many rows you need to go through.
CAUTION
Some properties of Columns and Rows require contiguous rows and columns 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
But if the ranges are grouped separately, Range("A1:B9", "C10:D19").Rows.Count the answer would be 19.