Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA Go to last empty row

Tags:

excel

vba

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

like image 220
r.paul Ǿ Avatar asked Dec 03 '22 21:12

r.paul Ǿ


2 Answers

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.

like image 171
AdmiralWen Avatar answered Dec 22 '22 23:12

AdmiralWen


try this:

Sub test()

With Application.WorksheetFunction
    Cells(.CountA(Columns("A:A")) + 1, 1).Select
End With

End Sub

Hope this works for you.

like image 31
L42 Avatar answered Dec 22 '22 22:12

L42