VBA 用数据识别第一行和最后一行

Question

我想要第一行和最后一行包含非空白单元格。我的最后一行工作正常，第一行是半身像。建议表示赞赏。

Sub idDataRange()

Dim firstRow As Long
Dim lastRow As Long

Sheets("fileNames").Select

' this has been adapted from a Stack overflow answer. lastRow unedited
' first row I changed Up to Down = NOT the solution!
With ActiveSheet
    firstRow = .Range("B" & .Rows.Count).End(xlDown).row
    lastRow = .Range("B" & .Rows.Count).End(xlUp).row
    End With

    MsgBox "the first row is " & firstRow
    MsgBox "last row is " & lastRow

End Sub

Answer 1

如果 B 列中的值不是来自公式，那么您可以使用 SpecialCells():

Dim firstRow As Long
Dim lastRow As Long

With Sheets("fileNames").Columns("B") '<--| reference your sheet (activating it is bad practice!) column "B" range
    If WorksheetFunction.CountA(.Cells) = 0 Then '<--| if no data whatever
        MsgBox "Sorry: no data"
    Else
        With .SpecialCells(xlCellTypeConstants) '<--| reference its cells with constant (i.e, not derived from formulas) values)
            firstRow = .Areas(1).Row
            lastRow = .Areas(.Areas.Count).Cells(.Areas(.Areas.Count).Rows.Count).Row
        End With
        MsgBox "the first row is " & firstRow
        MsgBox "last row is " & lastRow
    End If
End With