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.
Forum Discussion
Understand .Cells(.Rows.Count,“A”).End(xlUp).row
- It is the same as going to row 1 million and then pressing control+arrow-up key.
- Cell(Rows.Count, "A") means that your reference is a cell on column "A" at last row of your sheet "Rows.Count"
- 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
- 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
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
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.
