当我想要找到最后一个使用的单元格的值时,我使用:
Dim LastRow As Long
LastRow = Range("E4:E48").End(xlDown).Row
Debug.Print LastRow
当我将单个元素放入单元格时,输出结果不正确。但是,当我将多个值放入单元格时,则输出结果正确。这是什么原因?
当我想要找到最后一个使用的单元格的值时,我使用:
Dim LastRow As Long
LastRow = Range("E4:E48").End(xlDown).Row
Debug.Print LastRow
当我将单个元素放入单元格时,输出结果不正确。但是,当我将多个值放入单元格时,则输出结果正确。这是什么原因?
注意: 我打算把这篇文章变成一个“一站式”的帖子,您可以使用正确的
方法来查找最后一行。 这也将涵盖在查找最后一行时要遵循的最佳实践。 因此,每当我遇到新的情况/信息时,我将不断更新它。
下面列出了一些最常用但极不可靠的查找最后一行的方式,因此不应使用。
UsedRange
绝不能用来查找最后一个有数据的单元格。 它是非常不可靠的。 试试这个实验。
在单元格A5
中输入一些内容。现在,当您使用以下任何一种方法计算最后一行时,它都会给您5. 现在将单元格A10
着色为红色。如果您现在使用以下任何一种代码,您仍将获得5。如果您使用Usedrange.Rows.Count
,您会得到什么?它将不是5.
这里是一个情景来展示UsedRange
是如何工作的。
xlDown
同样不可靠。
考虑以下代码
lastrow = Range("A1").End(xlDown).Row
如果只有一个单元格(A1
)中有数据会发生什么?您将到达工作表中的最后一行!就像选择单元格A1
,然后按下End键,再按下Down Arrow键一样。如果范围内存在空单元格,这也会给您带来不可靠的结果。
CountA
也是不可靠的,因为如果中间存在空单元格,它将给出错误的结果。
因此,应避免使用UsedRange
、xlDown
和CountA
来查找最后单元格。
要查找E列中的最后一行,请使用以下方法:
With Sheets("Sheet1")
LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With
如果你注意到我们在 Rows.Count
前面有一个.
。通常情况下我们选择忽略它。请参考此处关于可能出现的错误。我总是建议在使用Rows.Count
和Columns.Count
时加上.
。那个问题是一个典型的场景,代码将因为Rows.Count
返回65536
(Excel 2003或更早版本)或者1048576
(Excel 2007或更新版本)而失败。同样地,Columns.Count
分别返回256
和16384
。
Excel 2007+有1048576
行的事实也强调了我们应该总是声明保存行数值的变量为Long
,而不是Integer
,否则你会得到Overflow
错误。
请注意,这种方法将跳过任何隐藏行。回顾一下我上面列A的屏幕截图,如果第8行被隐藏,则此方法将返回5
而不是8
。
要查找工作表中的Effective
末行,请使用此代码。请注意使用Application.WorksheetFunction.CountA(.Cells)
。因为如果工作表中没有带数据的单元格,那么.Find
会给你Run Time Error 91: Object Variable or With block variable not set
。
With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lastrow = 1
End If
End With
同样的原则适用于例如获取表格第三列中的最后一行:
Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1") 'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")
With tbl.ListColumns(3).Range
lastrow = .Find(What:="*", _
After:=.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
End Sub
Usedrange.Rows.Count
,你会得到什么?它不会是5。Usedrange查找最后一行非常不可靠。 - Siddharth Rout记住
了上次的设置。即使你手动进行一次“查找”,它也会记住上次的设置,如果知道这个“事实”,实际上这是个福音。 - Siddharth RoutUsedRange
的目的不同于上面答案中提到的目的。Used = 非空白,即含有数据。
Used = “…使用中”,表示包含数据或格式的部分。根据官方文档,这是 Excel 在保存时使用的标准。另请参阅 此官方文档。如果不了解此标准,可能会产生意想不到的结果,但也可能会被有意利用(当然较少),例如突出显示或打印特定区域,这些区域最终可能没有数据。当然,在保存工作簿时,使用该范围作为标准非常理想,以免丢失部分工作。
Used = “…使用中”,表示包含数据、格式或条件格式的部分。与2相同,但还包括任何条件格式规则的目标单元格。
如何找到最后一个使用的单元格取决于您的标准。
对于标准1,我建议阅读这个答案。请注意,UsedRange
被引用为不可靠。我认为这是误导性的(即对UsedRange
“不公平”),因为UsedRange
并不意味着报告包含数据的最后一个单元格。因此,在这种情况下不应使用它,正如该答案所示。也请参阅此评论。
UsedRange
是最可靠的选择。它甚至不需要保存工作簿以确保最后一个单元格已更新。Ctrl+End在保存之前会跳转到错误的单元格(“在保存工作表之前,最后一个单元格不会重置”,来自http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx。这是一个旧的参考文献,但在这方面仍然有效)。UsedRange
或Ctrl+End检测到。在图中,最后一个单元格是B3,因为显式地应用了格式。单元格B6:D7具有从条件格式规则派生的格式,即使使用UsedRange
也无法检测到。考虑到这一点需要一些VBA编程。
关于您提出的具体问题: 这背后的原因是什么?
您的代码使用范围E4:E48中的第一个单元格作为跳板,向下跳并使用End(xlDown)
。
如果除了第一个单元格以外的范围中没有非空白单元格,则会得到“错误”的输出。然后,您就像在黑暗中跳跃一样,即向下跳跃工作表(您应该注意空白和空字符串之间的区别!)。
请注意:
如果您的范围包含不连续的非空白单元格,则它也会给出错误的结果。
如果只有一个非空白单元格,但它不是第一个单元格,则您的代码仍将给出正确的结果。
我创建了这个一站式功能,用于确定最后一行、列和单元格的位置,无论是针对数据、格式化(分组/注释/隐藏)单元格还是条件格式化。
Sub LastCellMsg()
Dim strResult As String
Dim lngDataRow As Long
Dim lngDataCol As Long
Dim strDataCell As String
Dim strDataFormatRow As String
Dim lngDataFormatCol As Long
Dim strDataFormatCell As String
Dim oFormatCond As FormatCondition
Dim lngTempRow As Long
Dim lngTempCol As Long
Dim lngCFRow As Long
Dim lngCFCol As Long
Dim strCFCell As String
Dim lngOverallRow As Long
Dim lngOverallCol As Long
Dim strOverallCell As String
With ActiveSheet
If .ListObjects.Count > 0 Then
MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
Exit Sub
End If
strResult = "Workbook name: " & .Parent.Name & vbCrLf
strResult = strResult & "Sheet name: " & .Name & vbCrLf
'DATA:
'last data row
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lngDataRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lngDataRow = 1
End If
'strResult = strResult & "Last data row: " & lngDataRow & vbCrLf
'last data column
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lngDataCol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Else
lngDataCol = 1
End If
'strResult = strResult & "Last data column: " & lngDataCol & vbCrLf
'last data cell
strDataCell = Replace(Cells(lngDataRow, lngDataCol).Address, "$", vbNullString)
strResult = strResult & "Last data cell: " & strDataCell & vbCrLf
'FORMATS:
'last data/formatted/grouped/commented/hidden row
strDataFormatRow = StrReverse(Split(StrReverse(.UsedRange.Address), "$")(0))
'strResult = strResult & "Last data/formatted row: " & strDataFormatRow & vbCrLf
'last data/formatted/grouped/commented/hidden column
lngDataFormatCol = Range(StrReverse(Split(StrReverse(.UsedRange.Address), "$")(1)) & "1").Column
'strResult = strResult & "Last data/formatted column: " & lngDataFormatCol & vbCrLf
'last data/formatted/grouped/commented/hidden cell
strDataFormatCell = Replace(Cells(strDataFormatRow, lngDataFormatCol).Address, "$", vbNullString)
strResult = strResult & "Last data/formatted cell: " & strDataFormatCell & vbCrLf
'CONDITIONAL FORMATS:
For Each oFormatCond In .Cells.FormatConditions
'last conditionally-formatted row
lngTempRow = CLng(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(0)))
If lngTempRow > lngCFRow Then lngCFRow = lngTempRow
'last conditionally-formatted column
lngTempCol = Range(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(1)) & "1").Column
If lngTempCol > lngCFCol Then lngCFCol = lngTempCol
Next
'no results are returned for Conditional Format if there is no such
If lngCFRow <> 0 Then
'strResult = strResult & "Last cond-formatted row: " & lngCFRow & vbCrLf
'strResult = strResult & "Last cond-formatted column: " & lngCFCol & vbCrLf
'last conditionally-formatted cell
strCFCell = Replace(Cells(lngCFRow, lngCFCol).Address, "$", vbNullString)
strResult = strResult & "Last cond-formatted cell: " & strCFCell & vbCrLf
End If
'OVERALL:
lngOverallRow = Application.WorksheetFunction.Max(lngDataRow, strDataFormatRow, lngCFRow)
'strResult = strResult & "Last overall row: " & lngOverallRow & vbCrLf
lngOverallCol = Application.WorksheetFunction.Max(lngDataCol, lngDataFormatCol, lngCFCol)
'strResult = strResult & "Last overall column: " & lngOverallCol & vbCrLf
strOverallCell = Replace(.Cells(lngOverallRow, lngOverallCol).Address, "$", vbNullString)
strResult = strResult & "Last overall cell: " & strOverallCell & vbCrLf
MsgBox strResult
Debug.Print strResult
End With
End Sub
结果看起来像这样:
如需更详细的结果,可以取消代码中的某些行的注释:
存在一个限制-如果表格中存在表格,则结果可能不可靠,因此我决定在这种情况下避免运行代码:
If .ListObjects.Count > 0 Then
MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
Exit Sub
End If
由于原问题是关于查找最后一个单元格的问题,在这个答案中,我将列出各种可能导致意外结果的方法;请参见我的回答“如何使用宏在Excel表格中找到包含数据的最后一行?”以了解我的解决方案。
我将扩展sancho.s的答案和GlennFromIowa的评论,并添加更多细节:
[...] 首先必须确定什么被认为是使用过。我至少看到6种含义。单元格具有:
- 1)数据,即公式,可能导致空值;
- 2)值,即非空公式或常量;
- 3)格式;
- 4)条件格式;
- 5)与单元格重叠的形状(包括注释);
- 6)涉及表格(列表对象)。
你想要测试哪种组合?有些情况(如表格)可能更难测试,有些可能很少见(例如数据范围之外的形状),但其他情况可能会因情况而异(例如带有空值的公式)。
你可能还想考虑以下事项:
考虑到这一点,让我们看看常见获取“最后一个单元格”的方法如何产生意外结果:
.End(xlDown)
代码最容易出错(例如只有一个非空单元格或者在中间存在空单元格),原因在于Siddharth Rout的答案中解释了这个方法的不可靠性(搜索"“xlDown is equally unreliable.”"CountA
或Cells*.Count
)或.CurrentRegion
的解决方案也会在存在空单元格或行时出错。.End(xlUp)
,就像CTRL+UP一样,在可见行中查找数据(产生空值的公式被认为是“数据”)。使用它时必须注意避免标准陷阱(有关详细信息,请参见Siddharth Rout的答案中的"Find Last Row in a Column"部分),例如硬编码最后一行(Range("A65536").End(xlUp)
)而不是依赖sht.Rows.Count
。.SpecialCells(xlLastCell)
相当于CTRL+END,返回“使用范围”的最下方和最右边的单元格,因此所有依赖于“使用范围”的警告也适用于这种方法。此外,“使用范围”仅在保存工作簿和访问worksheet.UsedRange
时重置,因此xlLastCell
可能会在未保存的修改(例如删除一些行后)中产生陈旧的结果⚠️。请参见dotNET的附近答案。sht.UsedRange
(在sancho.s的答案中详细描述)考虑数据和格式(但不包括条件格式),并重置工作表的“使用范围”,这可能是您想要的,也可能不是您想要的。请注意,常见的错误是使用.UsedRange.Rows.Count
⚠️,它返回使用范围中的行数,而不是最后一行号(如果前几行为空,则它们将不同),有关详细信息,请参见newguy对How can I find last row that contains data in the Excel sheet with a macro?的回答
.Find
允许您查找任何列中的具有任何数据(包括公式)或非空值的最后一行。您可以选择您感兴趣的公式或值,但问题是它会重置Excel的查找对话框的默认设置️️⚠️,这可能会对您的用户产生极大的困惑。它还需要小心使用,请参见Siddharth Rout的答案中的"Find Last Row in a Sheet"部分。Cells
比重用Excel函数慢(虽然仍然可以高效),但允许您精确指定要查找的内容。请参见我的解决方案,它基于UsedRange
和VBA数组来查找给定列中具有数据的最后一个单元格--它处理隐藏行、过滤器、空白,不修改Find默认值,并且相当高效。Long
而不是Integer
来存储行号(避免超过65k行时出现Overflow
)Dim ws As Worksheet ... ws.Range(...)
而不是Range(...)
).Value
(它是一个Variant
)时,避免隐式转换,例如.Value <> ""
,因为如果单元格包含错误值,则会失败。lastrow = .Range("b" & .Rows.Count).End(xlUp).Row
这段代码是在做什么?是找到最后一行,然后从末尾向上移动吗? - Timo在使用该解决方案时需要记住的一点重要说明是...
LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
...的目的是确保您的LastRow
变量是Long
类型:
Dim LastRow as Long
否则,在 .XLSX 工作簿的某些情况下,您将最终得到溢出错误。Private Function FindLastRow(ws As Worksheet) As Long
' --------------------------------------------------------------------------------
' Find the last used Row on a Worksheet
' --------------------------------------------------------------------------------
If WorksheetFunction.CountA(ws.Cells) > 0 Then
' Search for any entry, by searching backwards by Rows.
FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
End Function
我想知道为什么没有人提到这个,但获取最后使用的单元格最简单的方法是:
Function GetLastCell(sh as Worksheet) As Range
GetLastCell = sh.Cells(1,1).SpecialCells(xlLastCell)
End Function
这实际上返回与选择单元格A1
后按下Ctrl+End得到的相同单元格。
需要注意的是:Excel会跟踪工作表中最右下角使用过的单元格。因此,如果您例如在B3输入某些内容,然后在H8输入其他内容,然后稍后删除H8的内容,则按下Ctrl+End仍将带您到H8单元格。上述功能将具有相同的行为。
Cells(1,1).Select()
是无效的,它可能是 ActiveSheet.Cells(1,1).Select
;此外,在 VBA 中不建议使用 Select
;)。 - shA.t凭借 Excel 的新的 计算引擎和数组功能,以及筛选函数,我相信这个主题现在应该不再存在争议,下面的选项提供了最佳的速度、可靠性和简单性的混合(过去很难平衡,因为许多帖子都有所体现)。
另外,我将“上次使用”定义为“非空”,如由isBlank函数定义。
首先,请注意,使用筛选函数可以更轻松地获取特定行或列(在本例中为列 A
或行 1
)的最后单元格,使用以下公式:
=MAX(FILTER(ROW(A:A),NOT(ISBLANK(A:A))))
=MAX(FILTER(COLUMN(1:1),NOT(ISBLANK(1:1))))
可以使用上述函数将其转换为VBA函数,并通过限制范围来使其更快,同时通过多列进行扩展(感谢Chris Neilsen的即时反馈和建议)。我还发现通过将每个列的作用范围限定为仅包括上一个最后一行以上的行,可以大幅提高速度。
Function FindLastRowInRange(someColumns As Range) As Long
Const zFx = "=MAX(FILTER(ROW(????),NOT(ISBLANK(????)),0))"
Dim tRng As Range, i As Long, tRow As Long, pRng As Range
With someColumns.Worksheet
Set tRng = Intersect(someColumns.EntireColumn, .UsedRange)
For i = 1 To tRng.Columns.Count
Set pRng = Intersect(tRng.Columns(i), _
Range(.Rows(FindLastRowInRange + 1), .Rows(.Rows.Count)))
If Not pRng Is Nothing Then
tRow = .Evaluate(Replace(zFx, "????", _
pRng.Address, 1, -1))
If tRow > FindLastRowInRange Then _
FindLastRowInRange = tRow
End If
Next i
End With
End Function
若要考虑整个工作表(所有列),建议使用另一种引用先前公式的VBA公式,但是这种公式是一个挥发性函数。这确保了该公式会随着工作表上任何更改而更新。显然,可以组合这两个公式,但我更喜欢限制使用挥发性函数。
Function FindLastRowInSheet(anywhereInSheet As Range) As Long
Application.Volatile
FindLastRowInSheet = FindLastRowInRange(anywhereInSheet.Worksheet.UsedRange)
End Function
xlup
那样可能遗漏隐藏行的风险。查找
设置。希望这可以结束争论,但如果有人发现其它不足之处,请分享。
Worksheet.Evaluate
方法 - zWS.Evaluate
2)这样做,您可能不需要使用 WS 名称限定范围,从而避免一些字符串操作(速度更快)3)我没有看到处理空列的 IfError
处理4)可能不需要在 tangoRange
的每一列上进行交集。只需使用 tangoRange.Columns(i)
5)可能需要处理非连续的 someColumns
6)您是否进行了任何性能测试? - chris neilsentRng
所使用的范围。然后,我将搜索范围缩小到pRng
(首选范围)内,以便在当前最大值以下但仍在tRng
内。起初,我不喜欢每次迭代重新定义搜索范围的想法,但事实证明这对我正在运行的一些荒谬测试来说是一个巨大的性能提升(而且仍然是)。我可以通过存储使用范围的最后一行而不是不断计数来优化它...所以这是一个小改进的好主意。在下一次迭代中。 - pgSystemTestersub last_filled_cell()
msgbox range("A65536").end(xlup).row
end sub
这里,A65536 是在Excel 2003上测试的A列中的最后一个单元格。
然而,这个问题是想要使用VBA找到最后一行,我认为包括一个数组公式作为工作表函数会更好,因为它经常被访问:
{=ADDRESS(MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0),COLUMN(D:D))}
{=MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0)}