根据您的范围内是否有空格(使用方法1),或者更简单地查找最后一个使用的单元格(使用方法3),有几个选项可供选择。
以下是以活动工作表的A列为例的选项:
1. SpecialCells
如果空单元格确实为空,则可以使用SpecialCells
来处理公式单元格(以=
开头)和/或常量单元格。
Sub GetNonEmtpy()
Dim rng1 As Range
Dim rng2 As Range
On Error Resume Next
Set rng1 = Columns("A").SpecialCells(xlConstants)
Set rng2 = Columns("A").SpecialCells(xlFormulas)
On Error GoTo 0
If Not rng1 Is Nothing Then MsgBox "Constants in " & rng1.Address(0, 0)
If Not rng2 Is Nothing Then MsgBox "formula in " & rng2.Address(0, 0)
'then work with these ranges
End Sub
2. 最后一个单元格查找
Sub LastCellLookup()
Dim rng1 As Range
Set rng1 = Cells(Rows.Count, "A").End(xlUp)
If rng1.Row <> 1 Then
MsgBox "last cell is " & rng1.Address(0, 0)
Else
'check first cell is not empty
If Len(rng1.Value) > 0 Then
MsgBox "last cell is " & rng1.Address(0, 0)
Else
MsgBox "row is blank"
End If
End If
End Sub
3. 查找
Sub LastCellFind()
Dim rng1 As Range
Set rng1 = Columns("A").Find("*", [a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then MsgBox "Last cell is " & rng1.Address(0, 0)
End Sub