在工作表中查詢最後一個非空列
Private Sub Get_Last_Used_Row_Index()
Dim wS As Worksheet
Set wS = ThisWorkbook.Sheets("Sheet1")
Debug.Print LastCol_1(wS)
Debug.Print LastCol_0(wS)
End Sub
你可以選擇 2 個選項,如果你想知道工作表中是否沒有資料:
- NO:使用 LastCol_1:你可以在
wS.Cells(...,LastCol_1(wS))
中直接使用它 - 是:使用 LastCol_0:在使用之前,你需要測試從函式獲得的結果是否為 0
Public Function LastCol_1(wS As Worksheet) As Double
With wS
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastCol_1 = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Else
LastCol_1 = 1
End If
End With
End Function
函式退出時,Err 物件的屬性會自動重置為零。
Public Function LastCol_0(wS As Worksheet) As Double
On Error Resume Next
LastCol_0 = wS.Cells.Find(What:="*", _
After:=ws.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function