Forum Discussion
Understand .Cells(.Rows.Count,“A”).End(xlUp).row
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.
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