VBA Go to last empty row
Solution 1
If you are certain that you only need column A, then you can use an End function in VBA to get that result.
If all the cells A1:A100 are filled, then to select the next empty cell use:
Range("A1").End(xlDown).Offset(1, 0).Select
Here, End(xlDown) is the equivalent of selecting A1 and pressing Ctrl + Down Arrow.
If there are blank cells in A1:A100, then you need to start at the bottom and work your way up. You can do this by combining the use of Rows.Count and End(xlUp), like so:
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Going on even further, this can be generalized to selecting a range of cells, starting at a point of your choice (not just in column A). In the following code, assume you have values in cells C10:C100, with blank cells interspersed in between. You wish to select all the cells C10:C100, not knowing that the column ends at row 100, starting by manually selecting C10.
Range(Selection, Cells(Rows.Count, Selection.Column).End(xlUp)).Select
The above line is perhaps one of the more important lines to know as a VBA programmer, as it allows you to dynamically select ranges based on very few criteria, and not be bothered with blank cells in the middle.
Solution 2
try this:
Sub test()
With Application.WorksheetFunction
Cells(.CountA(Columns("A:A")) + 1, 1).Select
End With
End Sub
Hope this works for you.
Solution 3
This does it:
Do
c = c + 1
Loop While Cells(c, "A").Value <> ""
'prints the last empty row
Debug.Print c
Related videos on Youtube
Comments
-
r.paul Ǿ over 3 years
I have a project on excel macro, I need to highlight the next last row that has an empty value. example cell A1:A100 have data and the next cell is A101 is empty.
when user click a button it should highlight the cell A101...
-
r.paul Ǿ about 10 yearsWhat if I will start on a specific column example A13:A1000
-
Samuel Leiton almost 4 yearsIs this faster than the previous examples?
-
ManyCurrents over 3 yearsMaycow Moura, I feel compelled to congratulate you on your ELEGANT SIMPLICITY! Poetry. Thank you!
-
user1016274 almost 3 yearsbe warned, looping through cells on a sheet is by far the slowest way of all. Very bad programming style, simply don't. Besides, if there are empty cells in between it prints the row number one higher than the first empty cell, not the first empty cell at the bottom.
-
Excel Hero about 2 years@ManyCurrents Wrong! This solution is not elegant. It is the worst possible way to do this.
-
Maycow Moura about 2 yearsOh my god "@Excel Hero", I'm so so sorry, I didn't know that I should fit to your "elegance standards" before commenting here to help a friend. I'm so damn sorry (contains sarcasm)