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