Find the last row in a range

[Rick Rothstein]

Thanks Rick Rothstein the suggestion, unfortunately if run with Excel 2003 takes 10 seconds!!

I propose the following Sub

Sub MaxCol()
    Dim Coln As Long, Surface
    Surface = Array("B", "C", "G")
    For Coln = 0 To UBound(Surface)
        Surface(Coln) = Cells(Rows.Count, Surface(Coln)).End(xlUp).Row
    Next
    MsgBox Application.Max(Surface)
End Sub

I did notice a slight hesitation on my system, but nowhere near 10 seconds, that's for sure. Okay, let's put that aside and try something completly different (it should be fast)...

Sub GetMaxRow()
  Dim Ar As Range, Max As Long, MaxRow As Long
  For Each Ar In Range("A:C,G:G").Areas
    Max = Evaluate("MAX(ROW(" & Ar.Resize(Ar.Rows.Count - 1).Address & _
          ")*(" & Ar.Resize(Ar.Rows.Count - 1).Address & "<>""""))")
    If Max > MaxRow Then MaxRow = Max
  Next
  MsgBox MaxRow
End Sub