Sub GetLastNotNA()
Dim LastVal As String
Dim AR()
AR = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row()).Value
For i = UBound(AR) To 1 Step -1
If AR(i, 1) <> "NA" Then
LastVal = AR(i, 1)
Exit For
End If
Next i
MsgBox LastVal
End Sub
Sub GetLastNoLoop()
Dim LastVal As String
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row()
LastVal = Evaluate(Replace("Lookup(2,1/(A1:A#<>""NA""),A1:A#)", "#", LR))
MsgBox LastVal
End Sub
And before Rick does it, here's a non looping version.
And before Rick does it...
I probably would have written it this way instead though......here's a non looping version.
Code:Sub GetLastNoLoop() Dim LastVal As String Dim LR As Long LR = Range("A" & Rows.Count).End(xlUp).Row() LastVal = Evaluate(Replace("Lookup(2,1/(A1:A#<>""NA""),A1:A#)", "#", LR)) MsgBox LastVal End Sub
Sub GetLastNotNA()
Dim LastVal As Variant
LastVal = [A:A].Find("NA", , xlValues, xlWhole, , xlNext, , , False).Offset(-1)
MsgBox LastVal
End Sub
I agree... as it turns out, there is a lot of functionality hiding inside the Excel object model extensions to the core Visual Basic language.I love seeing these no-loop versions. They are super interesting.
To the small extent I am able to do so, you are quite welcome.Thanks for laying down the knowledge Rick!