I have not wondered how to find some of the greatest values. I am referring only to the presented code.
Executing the code will accelerate significantly if you search the array, not the worksheet cells.
If you can predict which condition is less frequently satisfied (If varArr (i, 1) = 70 Then or If varArr (i, 36) = "NewLate" Then), then it should be the first one.
BTW. Instead of Cells (1 + i, ...) it was enough to start the loop from 2
Dim wsRD As Worksheet
Dim LastRow As Long
Dim rg As Range
Dim i As Long
Dim lRank As Double 'or other type
Dim varArr As Variant
Set wsRD = ...
With wsRD
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rg = .Range(.Cells(2, 8), .Cells(LastRow, 43))
End With
varArr = rg.Value
For i = 1 To UBound(varArr)
If varArr(i, 1) = 70 Then '8-7=1 The 8-th column in the worksheet is the 1-st column in Array
If varArr(i, 36) = "NewLate" Then '43-7=36
If varArr(i, 17) > lRank Then '24-7=17
lRank = varArr(i, 17)
End If
End If
End If
Next i
MsgBox lRank
..::Edit
OK, I was thinking about finding a few maximum values considering the conditions. Something very similar to the previous code.
Dim wsRD As Worksheet
Dim LastRow As Long
Dim rg As Range
Dim i As Long
Dim varArr As Variant
Dim varArrToRank As Variant
Set wsRD = ...
With wsRD
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rg = .Range(.Cells(2, 8), .Cells(LastRow, 43))
End With
varArr = rg.Value
ReDim varArrToRank(1 To UBound(varArr))
For i = 1 To UBound(varArr)
If varArr(i, 1) = 70 Then
If varArr(i, 36) = "NewLate" Then
If varArr(i, 17) > lRank Then
varArrToRank(i) = varArr(i, 17)
End If
End If
End If
Next i
MsgBox "1-st Max = " & Application.Large(varArrToRank, 1) & vbLf & _
"2-nd Max = " & Application.Large(varArrToRank, 2) & vbLf & _
"3-th Max = " & Application.Large(varArrToRank, 3)
::..
Artik