Find the last row in a range

[PcMax]

Hi,

Use the following code to identify the last row of the range.
Can you recommend a better solution!



Dim Riga As Long
Riga = Application.WorksheetFunction.Max(Cells(Rows.Count , "A").End(xlUp).Row, _
Cells(Rows.Count, 2).End(xlUp).Row, Cells(Rows.Count, "C").End(xlUp).Row)
MsgBox Riga

Thanks in advance

[Rick Rothstein]

Use the following code to identify the last row of the range.
Can you recommend a better solution!



Dim Riga As Long
Riga = Application.WorksheetFunction.Max(Cells(Rows.Count , "A").End(xlUp).Row, _
Cells(Rows.Count, 2).End(xlUp).Row, Cells(Rows.Count, "C").End(xlUp).Row)
MsgBox Riga

Give this a try...

Riga = Columns("A:C").Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

And if you do not mind maintaining empty arguments, we can shorten that code line down to this by eliminating the named arguments...

Riga = Columns("A:C").Find("*", , xlFormulas, , xlRows, xlPrevious).Row

[PcMax]

Hi,

Thanks for the suggestions, I ask if there are alternatives in the event of non-adjacent columns.
I tried to change the range of research, but I can not use the method ..


Riga = Columns("A:C", "G").Find

[ashu1990]

riga = Range("A:C, G:G").find

[Rick Rothstein]

Thanks for the suggestions, I ask if there are alternatives in the event of non-adjacent columns.
I tried to change the range of research, but I can not use the method ..


Riga = Columns("A:C", "G").Find
ashu1990 gave you the correct syntax to use... Range("A:C,G:G") instead of Columns("A:C","G") because Columns cannot take non-contiguous ranges for its argument... however, you cannot use non-contiguous ranges with the Find function (well, you can, but it will end up finding a value in the last area of the non-contiguous range first and not look at the other areas making up the non-contiguous range, so you will not always get the correct answer doing so). I think you are back to your Max-method, but I would write it as a loop so that it is more flexible...

Dim Riga As Long, Ar As Range

For Each Ar In Range("A:C,G:G")
Riga = Application.Max(Riga, Ar(Ar.Count).End(xlUp).Row)
Next

MsgBox Riga

[PcMax]

Hi,


I think you are back to your Max-method, but I would write it as a loop so that it is more flexible...
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

[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

[PcMax]

Hi,

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


For Each Ar In Range("A:C,G:G")
Riga = Application.Max(Riga, Ar(Ar.Count).End(xlUp).Row)
Next
I tried the last time in a sheet with the cell K625:
Excel 2003 7,9 sec while Excel 2013 4,2 sec Pc New!

Remember that this code should work on a PC 800 mhz...

Thanks for all the suggestions received