迹忆客 专注技术分享

在处理电子表格中的数据时，我们需要知道数据的最后一行和最后一列。设置光标可以迭代的限制很有用。

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