VBA Go to last empty row

123,122

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
Share:
123,122

Related videos on Youtube

r.paul Ǿ
Author by

r.paul Ǿ

Student Programmer

Updated on May 21, 2020

Comments

  • r.paul Ǿ
    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 Ǿ
    r.paul Ǿ about 10 years
    What if I will start on a specific column example A13:A1000
  • Samuel Leiton
    Samuel Leiton almost 4 years
    Is this faster than the previous examples?
  • ManyCurrents
    ManyCurrents over 3 years
    Maycow Moura, I feel compelled to congratulate you on your ELEGANT SIMPLICITY! Poetry. Thank you!
  • user1016274
    user1016274 almost 3 years
    be 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
    Excel Hero about 2 years
    @ManyCurrents Wrong! This solution is not elegant. It is the worst possible way to do this.
  • Maycow Moura
    Maycow Moura about 2 years
    Oh 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)

Related