- The Range Object
- Syntax for Specifying a Range
- Named Ranges
- Shortcut for Referencing Ranges
- 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 have 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.