Posted By: Anonymous
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
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
ws is a Worksheet object. In the questions example it was implied that the statement was inside a
With ws lastRowIndex = .Cells(.Rows.Count, "A").End(xlUp).row End With
ws.Rows.Countreturns the total count of rows in the worksheet (1048576 in Excel 2010).
.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
- 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.
Range.End is not a trivial function.
.rowreturns the row index of that cell.