Home > Articles

Referring to Ranges

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.


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.

  • + Share This
  • 🔖 Save To Your Account