你可以使用它,但它很慢:
Sub LastRowWithNonNullData()
Dim LastRow As Variant, i As Long
For i = Rows.Count To 1 Step -1
If Cells(i, "A").Value <> "" Then
MsgBox i
Exit Sub
End If
Next i
End Sub
这不是很慢:
Sub IsThisAnyBetter()
MsgBox Evaluate("IF(COUNTA(A:A)=0,"""",MAX((A:A<>"""")*(ROW(A:A))))")
End Sub
两者都会忽略 A 列底部的Null . 如果直接在工作表单元格中使用Evaluate()参数,则必须输入数组 .
EDIT#1:
这两个子工作在 A 列上 . 假设我们要修改第二个子中的公式以处理 77 列(即列 BY ) . 这是一种方法:
Sub AnyColumn()
Dim s1 As String, s2 As String, iCol As Long, iColAlpha As String
Dim s3 As String, s4 As String, s5 As String
iCol = 77
s1 = "IF(COUNTA(A:A)=0,"""",MAX((A:A<>"""")*(ROW(A:A))))"
s2 = Cells(1, iCol).Address(0, 0)
s3 = Left(s2, Len(s2) - 1)
s4 = s3 & ":" & s3
s5 = Replace(s1, "A:A", s4)
MsgBox s5
End Sub
EDIT#2:
我在Evaluate()中使用的公式改编自Chip Perason