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.

Answer 3

The first part:

.Cells(.Rows.Count,"A")

Sends you to the bottom row of column A, which you knew already.

The End function starts at a cell and then, depending on the direction you tell it, goes that direction until it reaches the edge of a group of cells that have text. Meaning, if you have text in cells C4:E4 and you type:

Sheet1.Cells(4,"C").End(xlToRight).Select

The program will select E4, the rightmost cell with text in it.

In your case, the code is spitting out the row of the very last cell with text in it in column A. Does that help?

Answer 4

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

I think the first dot in the parenthesis should not be there, I mean, you should write it in this way:

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

Before the Cells, you can write your worksheet name, for example:

Worksheets("sheet1").Cells(Rows.Count, 2).End(xlUp).row

The worksheet name is not necessary when you operate on the same worksheet.