Find the last row in a range

Hi,

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

Code:

---

    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

Quote:

---

Originally Posted by PcMax

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

Code:

---

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

Code:

---

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

Code:

---

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

---

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

Code:

---

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

---

Code:

---

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

---

Quote:

---

Originally Posted by PcMax

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

Code:

---

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

Code:

---

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

---

Hi,

Quote:

---

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

Code:

---

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

---

Quote:

---

Originally Posted by PcMax

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

I propose the following Sub

Code:

---

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

Code:

---

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

---

Hi,

Quote:

---

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

---

Code:

---

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