在 VBA 中查詢最後一行和最後一列

1. 程式碼語法
2. 獲取 VBA 中當前工作表中資料的最後一行/列
3. 從 VBA 中的另一個工作表中獲取最後一行/列的資料
4. 使用函式獲取工作表的最後一行/列

在處理電子表格中的資料時，我們需要知道資料的最後一行和最後一列。設定游標可以迭代的限制很有用。

VBA 沒有內建函式來返回電子表格中使用的最後一行/列。但是通過使用簡單的 VBA 解決方案，我們可以使我們的使用者定義函式設計為返回工作表中使用的最後一行/列的值。

本文將演示如何使用 Excel VBA 查詢電子表格中使用的最後一行和最後一列。

程式碼語法

獲取最後一行的程式碼：

Cells(Rows.Count,[colNum]).End(xlUp).Row

獲取最後一列的程式碼：

Cells(Rows.Count,[rowNum]).End(xlUp).Row

在哪裡，

獲取 VBA 中當前工作表中資料的最後一行/列

下面的程式碼塊需要寫在 Microsoft Excel Objects 的目標工作表上。

Sub GetLastRowCol()

'Declare lastRow and Last Col as Long datatype as we need to presume the values will be _
assigned with these variables are very high numbers.
Dim lastRow as Long
Dim lastCol as Long

'Assigning lastRow and lastCol variable
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Debug.Print "The last Row is: " & lastRow & vbNewline & "The last Column is :" & lastCol 
End Sub

輸出：

The last Row is :1
The last Column is :1

從 VBA 中的另一個工作表中獲取最後一行/列的資料

在處理多個工作表時，我們需要明確指定我們當前所在的工作表。

下面的程式碼塊將演示如何從不同的工作表中獲取使用的最後一行/列。

Sub GetLastRowCol()

'Declare lastRow and Last Col as Long datatype as we need to presume the values will be _
assigned with these variables are very high numbers.
Dim lastRow as Long
Dim lastCol as Long

'Declaring workbook and worksheets
Dim wb as Workbook
Dim s1, s2 as Worksheet

'Initializing the wb, s1, and s2 objects.
Set wb = ThisWorkBook
Set s1 = wb.Sheets("Sheet1")
Set s2 = wb.Sheets("Sheet2")

'Will return the row number of the last row in Column A on Sheet1
lastRow = s1.Cells(s1.Rows.Count, 1).End(xlUp).Row
'Will return the row number of the last row in Row 1 on Sheet1
lastCol = s1.Cells(1, s1.Columns.Count).End(xlToLeft).Column

Debug.print "The last row in Sheet1:" & lastRow & vbNewline & "The last column in Sheet 1:" & lastCol

'Will return the row number of the last row in Column A on Sheet2
lastRow = s2.Cells(s2.Rows.Count, 1).End(xlUp).Row
'Will return the row number of the last row in Column A on Sheet2
lastCol = s2.Cells(1, s2.Columns.Count).End(xlToLeft).Column

Debug.print "The last row in Sheet2:" & lastRow & vbNewline & "The last column in Sheet 2:" & lastCol

End Sub

輸出：

The last row in Sheet1: 1
The last column in Sheet1: 1
The last row in Sheet2: 1
The last column in Sheet2: 1

使用函式獲取工作表的最後一行/列

下面的函式將根據 Worksheet 引數返回最後一行或最後一列。

Function GetLastRow(s As Worksheet) As Long
GetLastRow = s.Cells(s.Rows.Count, 1).End(xlUp).Row
End Function
Function GetLastCol(s As Worksheet) As Long
GetLastCol = s.Cells(1, s.Columns.Count).End(xlToLeft).Column
End Function

Sub testRow()
Dim wb As Workbook
Dim s1 As Worksheet

Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Debug.Print GetLastRow(s1)
End Sub

Sub testCol()
Dim wb As Workbook
Dim s1 As Worksheet

Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Debug.Print GetLastCol(s1)
End Sub

testRow 輸出：

1

testCol 輸出：

1