Попробуйте это:
Sub hjksdf()
Dim lastRow As Long
With ActiveSheet
lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With
For i = lastRow To 1 Step -1
If Cells(i, "B").Text <> "#N/A" Then
Exit For
End If
Next i
lastRow = i
MsgBox i
End Sub
EDIT # 1:
Вот способы найти последний Z и первый Z:
Sub FindLastZ()
Dim lastRow As Long
With ActiveSheet
lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With
For i = lastRow To 1 Step -1
If Cells(i, "B").Text = "Z" Then
Exit For
End If
Next i
lastRow = i
MsgBox i
End Sub
Sub FindFirstZ()
Dim lastRow As Long
With ActiveSheet
lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With
For i = 1 To lastRow
If Cells(i, "B").Text = "Z" Then
Exit For
End If
Next i
lastRow = i
MsgBox i
End Sub