How do You Set a Value to Equal The Greater Of Two Values? VBA Excel

[killpaddy]

Hello,

I have the following code inside a For loop:

        ARow1 = Range("A" & i).row
        ARow2 = Range("A" & "1:A" & z).Find(Trim(Range("C" & i).Value)).row
        CRow1 = Range("C" & i).row
        CRow2 = Range("C" & "1:C" & z).Find(Trim(Range("A" & i).Value)).row
        
        ARow = ?
        CRow = ?

I want 'ARow' to equal the greater of 'ARow1' and 'ARow2' (and the same for 'CRow') without having to use an If statement; it seems like the kind of thing that would have a VBA function already, but I dont know what it is. Can anyone help?

Also what would be the equivalent for the lesser of two values

Thanks

 

[JLGWhiz]

This seems to work:

Code:
Sub maxVal()
Dim rw As Long, rw1 As Long, rw2 As Long
rw1 = Rows(6).Row
rw2 = Rows(7).Row
rw = WorksheetFunction.Max(rw1, rw2)
MsgBox rw
End Sub
Code: