Understand .Cells(.Rows.Count,“A”).End(xlUp).row

[chancelin]

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.

[#2]

It is the same as going to row 1 million and then pressing control+arrow-up key.

[#3]

Good evening Mr. Jan Karel Pieterse, I still do not understand. Please be more explicit. Why use this piece of code? And also explain me each from this code

[#4]

I understand better now.
This line means: selects (.Select) the last non-empty cell (.End) up (xlUp) from the last cell (& Rows.Count) of column A (Range ("A"))
So this command line behaves exactly as if you were in the last cell of column A (cell A1048576 for the 2007 version of Excel) and you press CTRL + up arrow.
Why go from the bottom of the sheet to the top and not the other way around? Quite simply, to avoid a selection error if it turned out that the array contains empty cells.

[#5]

One reason to start from the bottom is if your column contains empty cells somewhere in the middle that is where the cursor would stop. Or if you are on the last filled cell and then hit control+down, you end up on row 1048576. Thus starting from row 1048576 and going up is a good idea.

[#6]

Very clear your comment.
Thank you Jan Karel Pieterse

[#7]

I see this is 2+ years old, please can I seek some clarifications
Please can I understand this way, the sequence of the execution is confusing me
Cells(.Rows.Count,“A”).End(xlUp).row
Please clear my understanding, Say cursor is at A1
Cells() is an object having the method End(), which is executed first so it goes to the end, while going to the end it calculates the rows ( or while traversing back to A1 ?) so now we have total rows for column A. Please can you explain me this and can I apply same logic to all functions..

[#8]

@chancelin

1. Cell(Rows.Count, "A") means that your reference is a cell on column "A" at last row of your sheet "Rows.Count"
2. End(xlUp) select the first or last filled row to the direction "Up", So if Cell(Rows.Count, "A") is an empty Cell it will go Up untill it finds a filled row
3. Row returns the number of the row based on the selected Cell

If you have a spread sheet with 1000 lines with data, the Cell(1000,"A") will be your last filled line, Cell(Rows.Count, "A").End(xlUp).Row will return 1000

Sub checkLastFilledLine()

Dim lastLine As Long

lastLine = Cell(Rows.Count, 1).End(xlUp).Row

msgbox lastLine

End Sub

The above Sub will pop up a message box with the number of the last line on column "A" that is not blank