- The Range Object
- Syntax to Specify a Range
- Named Ranges
- Shortcut for Referencing Ranges
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Use the Cells Property to Select a Range
- Use the Offset Property to Refer to a Range
- Use the Resize Property to Change the Size of a Range
- Use the Columns and Rows Properties to Specify a Range
- Use the Union Method to Join Multiple Ranges
- Use the Intersect Method to Create a New Range from Overlapping Ranges
- Use the ISEMPTY Function to Check Whether a Cell Is Empty
- Use the CurrentRegion Property to Select a Data Range
- Use the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
Use 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 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.