3

I know how to find the last row of my worksheet that has any type of data or values

Dim lastRow As Integer
   With ActiveSheet
       lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
   End With

Question: How do I identify that last row with actual data, in otherwords the last row that doesn't have "#N/A" value.

0

1 Answer 1

3

Give this a try:

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:

Here are ways to find the last Z and the first 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
4
  • Excellent, that gets me exactly what I need. I will then use this lastRow value to handle all of my page setup, printing and formatting. I'll probably rename the macro to something a bit more relevant though ;-)
    – dasMetzger
    Mar 19, 2015 at 16:17
  • @dasMetzger Please remember to use Long Mar 19, 2015 at 16:19
  • I saw that in another thread. What's the reason for using long here instead of integer or even range
    – dasMetzger
    Mar 19, 2015 at 16:24
  • @dasMetzger Integer is good to 32767. Long is good to 2147483647 Mar 19, 2015 at 16:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .