Get last word from column that is not "NA"

[eaje92]

Hi all, I need some help in using vba to get the last Letter/Word from a column while the values below it are set as "NA"

E.g

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]0A[/TD]
[/TR]
[TR]
[TD]1B[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]

In this case, the value of 1B will be copied/stored.

Thank you in advance!

 

[lrobbo314]

This code finds the last value that is not NA and displays it in a messagebox. It also assumes that your data is in column A.

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

 

[lrobbo314]

And before Rick does it, here's a non looping version.

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

 

[Ingolf]

And before Rick does it, here's a non looping version.

 

[Rick Rothstein]

And before Rick does it...

...here's a non looping version.

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

I probably would have written it this way instead though...

Sub GetLastNotNA()
  Dim LastVal As Variant
  LastVal = [A:A].Find("NA", , xlValues, xlWhole, , xlNext, , , False).Offset(-1)
  MsgBox LastVal
End Sub

 

[lrobbo314]

I love seeing these no-loop versions. They are super interesting. Thanks for laying down the knowledge Rick!

 

[Rick Rothstein]

I love seeing these no-loop versions. They are super interesting.

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.

Thanks for laying down the knowledge Rick!

To the small extent I am able to do so, you are quite welcome.