VBA Go to last empty row

Question

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...

Answer 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.

Answer 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.