Option Explicit
Sub lastUsedCells() 'optimised for performance
Dim maxRow As Long
Dim maxCol As Long
With ActiveSheet
If WorksheetFunction.CountA(.UsedRange) > 0 Then 'if sheet is not empty
With .UsedRange 'restrict search area to UsedRange; (includes formats)
maxRow = .Rows.Count + 1 'last row in UsedRange
maxCol = .Columns.Count + 1 'last col in UsedRange
'first cell not empty in col A
If Len(.Cells(1, 1)) = 0 Then MsgBox .Cells(1, 1).End(xlDown).Row
'first cell not empty in row 1
If Len(.Cells(1, 1)) = 0 Then MsgBox .Cells(1, 1).End(xlToRight).Column
MsgBox .Cells(maxRow, 1).End(xlUp).Row 'last row in column A
MsgBox .Cells(1, maxCol).End(xlToLeft).Column 'last column in row 1
MsgBox .Find( _
What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row 'last row (longest col)
MsgBox .Find( _
What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column 'last col (longest row)
End With
Else
MsgBox "Sheet " & .Name & " is empty"
End If
End With
End Sub
1 回答
一些可靠的方法来查找最后使用的包含工作表值的单元格:
.
更多细节:Finding last used cell in VBA