Getting Last Non-Blank Row Number With VBA

[Andy]

Hi Everyone

In Excel I can place the following in a cell to get the last non blank
row number in column A. This works great and it's super quick.

=ROW(OFFSET(A1,COUNTA(A:A)-1,0))

To do the same with VBA code seems very complex. Can you please show
me some VBA code that will give me the last non-blank row number for a
given column?

Andy

 

[Ken]

Andy

What your formula actually does is count how many non blanks there are
in column A, and give the row number of the cell that is offset one
less than that number from cell A1; which, if there are no blanks
cells in column A turns out to be the last non blank row number. If
there are any blanks, it won't give the number you want. To replicate
the functionality you described in VBA is pretty easy. All you have
to do is go to the bottom of the spreadsheet, end up to the last non
blank row with something in it. You don't say what you want to do
with the number, but

Sub Macro2()
MsgBox Cells(Rows.Count, 1).End(xlUp).Row
End Sub

will tell you what the number is.

I hope this helps.

Ken

 

[Andy]

Excellent!! Thanks Ken.