Meaning of .Cells(.Rows.Count,"A").End(xlUp).row

Question

I was just wondering if you could help me better understand what .Cells(.Rows.Count,"A").End(xlUp).row does. I understand the portion before the .End part.

Answer 1

It is used to find the how many rows contain data in a worksheet that contains data in the column "A". The full usage is

 lastRowIndex = ws.Cells(ws.Rows.Count, "A").End(xlUp).row 

Where ws is a Worksheet object. In the questions example it was implied that the statement was inside a With block

With ws     lastRowIndex = .Cells(.Rows.Count, "A").End(xlUp).row End With 

1. ws.Rows.Count returns the total count of rows in the worksheet (1048576 in Excel 2010).
2. .Cells(.Rows.Count, "A") returns the bottom most cell in column "A" in the worksheet

Then there is the End method. The documentation is ambiguous as to what it does.

Returns a Range object that represents the cell at the end of the region that contains the source range

Particularly it doesn't define what a "region" is. My understanding is a region is a contiguous range of non-empty cells. So the expected usage is to start from a cell in a region and find the last cell in that region in that direction from the original cell. However there are multiple exceptions for when you don't use it like that:

• If the range is multiple cells, it will use the region of rng.cells(1,1).
• If the range isn't in a region, or the range is already at the end of the region, then it will travel along the direction until it enters a region and return the first encountered cell in that region.
• If it encounters the edge of the worksheet it will return the cell on the edge of that worksheet.

So Range.End is not a trivial function.

4. .row returns the row index of that cell.

Answer 2

[A1].End(xlUp) [A1].End(xlDown) [A1].End(xlToLeft) [A1].End(xlToRight) 

is the VBA equivalent of being in Cell A1 and pressing Ctrl + Any arrow key. It will continue to travel in that direction until it hits the last cell of data, or if you use this command to move from a cell that is the last cell of data it will travel until it hits the next cell containing data.

If you wanted to find that last "used" cell in Column A, you could go to A65536 (for example, in an XL93-97 workbook) and press Ctrl + Up to "snap" to the last used cell. Or in VBA you would write:

Range("A65536").End(xlUp) which again can be re-written as Range("A" & Rows.Count).End(xlUp) for compatibility reasons across workbooks with different numbers of rows.