☰

Excel VBA：如何查找包含实际数据的工作表的最后一行

tag-icon • tag-icon microsoft-excel vba
Excel VBA：如何查找包含实际数据的工作表的最后一行

我知道如何找到工作表中包含任何类型的数据或值的最后一行

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

问题：我如何识别具有实际数据的最后一行，换句话说，没有“#N/A”值的最后一行。

答案1

尝试一下：

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

编辑＃1：

以下是查找最后的 是和第一的 是：

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

相关内容