首页 / 知识

关于excel：如何找到包含特定列中数据的最后一行？

2023-04-15 20:43:00

How can I find last row that contains data in a specific column?

如何找到特定列和特定工作表中包含数据的最后一行？

怎么样：

1
2
3
4
5
6

Function GetLastRow(strSheet, strColumn) As Long
Dim MyRange As Range

Set MyRange = Worksheets(strSheet).Range(strColumn &"1")
GetLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row
End Function

关于注释，即使最后一行中只有一个单元格有数据，这也会返回最后一个单元格的行号：

1	Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

您应该使用.End(xlup)，但可能不想使用65536，而是使用：

1	sheetvar.Rows.Count

这样，它适用于Excel 2007，我相信它具有65536行以上

简单快捷：

1
2
3

Dim lastRow as long
Range("A1").select
lastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

使用示例：

1
2
3
4
5

cells(lastRow,1)="Ultima Linha, Last Row. Youpi!!!!"

'or

Range("A" & lastRow).Value ="FIM, THE END"

1
2
3
4
5
6
7
8
9
10
11
12
13
14

function LastRowIndex(byval w as worksheet, byval col as variant) as long
dim r as range

set r = application.intersect(w.usedrange, w.columns(col))
if not r is nothing then
set r = r.cells(r.cells.count)

if isempty(r.value) then
LastRowIndex = r.end(xlup).row
else
LastRowIndex = r.row
end if
end if
end function

用法：

1 2	? LastRowIndex(ActiveSheet, 5) ? LastRowIndex(ActiveSheet,"AI")

所有依赖于内置行为的解决方案(例如.Find和.End)都存在没有充分记录的限制(有关详细信息，请参阅我的其他答案)。

我需要一些东西：

查找特定列中的最后一个非空单元格(即具有任何公式或值，即使它是一个空字符串)
依靠行为明确的原语
与自动过滤器和用户修改一起可靠地工作
在10,000行上尽可能快地运行(要在Worksheet_Change处理程序中运行而不会变慢)
...性能不会因意外数据或格式置于表格末尾(约100万行)而下降

下面的解决方案：

使用UsedRange查找行号的上限(通常在接近使用范围的末尾时快速搜索真正的"最后一行")；
向后查找给定列中包含数据的行；
...使用VBA数组来避免单独访问每一行(如果UsedRange中有很多行，我们需要跳过)

(没有测试，对不起)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

' Returns the 1-based row number of the last row having a non-empty value in the given column (0 if the whole column is empty)
Private Function getLastNonblankRowInColumn(ws As Worksheet, colNo As Integer) As Long
' Force Excel to recalculate the"last cell" (the one you land on after CTRL+END) /"used range"
' and get the index of the row containing the"last cell". This is reasonably fast (~1 ms/10000 rows of a used range)
Dim lastRow As Long: lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row - 1 ' 0-based

' Since the"last cell" is not necessarily the one we're looking for (it may be in a different column, have some
' formatting applied but no value, etc), we loop backward from the last row towards the top of the sheet).
Dim wholeRng As Range: Set wholeRng = ws.Columns(colNo)

' Since accessing cells one by one is slower than reading a block of cells into a VBA array and looping through the array,
' we process in chunks of increasing size, starting with 1 cell and doubling the size on each iteration, until MAX_CHUNK_SIZE is reached.
' In pathological cases where Excel thinks all the ~1M rows are in the used range, this will take around 100ms.
' Yet in a normal case where one of the few last rows contains the cell we're looking for, we don't read too many cells.
Const MAX_CHUNK_SIZE = 2 ^ 10 ' (using large chunks gives no performance advantage, but uses more memory)
Dim chunkSize As Long: chunkSize = 1
Dim startOffset As Long: startOffset = lastRow + 1 ' 0-based
Do ' Loop invariant: startOffset>=0 and all rows after startOffset are blank (i.e. wholeRng.Rows(i+1) for i>=startOffset)
startOffset = IIf(startOffset - chunkSize >= 0, startOffset - chunkSize, 0)
' Fill `vals(1 To chunkSize, 1 To 1)` with column's rows indexed `[startOffset+1 .. startOffset+chunkSize]` (1-based, inclusive)
Dim chunkRng As Range: Set chunkRng = wholeRng.Resize(chunkSize).Offset(startOffset)
Dim vals() As Variant
If chunkSize > 1 Then
vals = chunkRng.Value2
Else ' reading a 1-cell range requires special handling <http://www.cpearson.com/excel/ArraysAndRanges.aspx>
ReDim vals(1 To 1, 1 To 1)
vals(1, 1) = chunkRng.Value2
End If

Dim i As Long
For i = UBound(vals, 1) To LBound(vals, 1) Step -1
If Not IsEmpty(vals(i, 1)) Then
getLastNonblankRowInColumn = startOffset + i
Exit Function
End If
Next i

If chunkSize < MAX_CHUNK_SIZE Then chunkSize = chunkSize * 2
Loop While startOffset > 0

getLastNonblankRowInColumn = 0
End Function

这是查找最后一行，最后一列或最后一个单元格的解决方案。它解决了找到的列的A1 R1C1参考样式难题。希望我能给予荣誉，但找不到/记住我从哪里得到的，所以谢谢！谁将原始代码发布到任何地方的人。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

Sub Macro1
Sheets("Sheet1").Select
MsgBox"The last row found is:" & Last(1, ActiveSheet.Cells)
MsgBox"The last column (R1C1) found is:" & Last(2, ActiveSheet.Cells)
MsgBox"The last cell found is:" & Last(3, ActiveSheet.Cells)
MsgBox"The last column (A1) found is:" & Last(4, ActiveSheet.Cells)
End Sub

Function Last(choice As Integer, rng As Range)
' 1 = last row
' 2 = last column (R1C1)
' 3 = last cell
' 4 = last column (A1)
Dim lrw As Long
Dim lcol As Integer

Select Case choice
Case 1:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

Case 2:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0

Case 3:
On Error Resume Next
lrw = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lcol = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Last = Cells(lrw, lcol).Address(False, False)
If Err.Number > 0 Then
Last = rng.Cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0
Case 4:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Last = R1C1converter("R1C" & Last, 1)
For i = 1 To Len(Last)
s = Mid(Last, i, 1)
If Not s Like"#" Then s1 = s1 & s
Next i
Last = s1

End Select

End Function

Function R1C1converter(Address As String, Optional R1C1_output As Integer, Optional RefCell As Range) As String
'Converts input address to either A1 or R1C1 style reference relative to RefCell
'If R1C1_output is xlR1C1, then result is R1C1 style reference.
'If R1C1_output is xlA1 (or missing), then return A1 style reference.
'If RefCell is missing, then the address is relative to the active cell
'If there is an error in conversion, the function returns the input Address string
Dim x As Variant
If RefCell Is Nothing Then Set RefCell = ActiveCell
If R1C1_output = xlR1C1 Then
x = Application.ConvertFormula(Address, xlA1, xlR1C1, , RefCell) 'Convert A1 to R1C1
Else
x = Application.ConvertFormula(Address, xlR1C1, xlA1, , RefCell) 'Convert R1C1 to A1
End If
If IsError(x) Then
R1C1converter = Address
Else
'If input address is A1 reference and A1 is requested output, then Application.ConvertFormula
'surrounds the address in single quotes.
If Right(x, 1) ="'" Then
R1C1converter = Mid(x, 2, Len(x) - 2)
Else
x = Application.Substitute(x,"$","")
R1C1converter = x
End If
End If
End Function

1
2
3

Public Function LastData(rCol As Range) As Range
Set LastData = rCol.Find("*", rCol.Cells(1), , , , xlPrevious)
End Function

用法：?lastdata(activecell.EntireColumn).Address

我想使用UsedRange添加一种更可靠的方法来查找最后使用的行：

1	lastRow = Sheet1.UsedRange.Row + Sheet1.UsedRange.Rows.Count - 1

同样，找到最后使用的列，您可以看到

enter image description here

立即窗口中的结果：

1 2	?Sheet1.UsedRange.Row+Sheet1.UsedRange.Rows.Count-1 21

1	Last_Row = Range("A1").End(xlDown).Row

只是为了验证，假设您要使用单元格C1中的数据打印最后一行的行号。

1
2
3

Range("C1").Select
Last_Row = Range("A1").End(xlDown).Row
ActiveCell.FormulaR1C1 = Last_Row

1
2
3
4
5
6
7
8
9
10
11
12
13

Public Function GetLastRow(ByVal SheetName As String) As Integer
Dim sht As Worksheet
Dim FirstUsedRow As Integer 'the first row of UsedRange
Dim UsedRows As Integer ' number of rows used

Set sht = Sheets(SheetName)
''UsedRange.Rows.Count for the empty sheet is 1
UsedRows = sht.UsedRange.Rows.Count
FirstUsedRow = sht.UsedRange.Row
GetLastRow = FirstUsedRow + UsedRows - 1

Set sht = Nothing
End Function

sheet.UsedRange.Rows.Count：重复使用的行数，不包括使用的第一行上方的空行

如果第1行为空，最后使用的行为10，UsedRange.Rows.Count将返回9，而不是10。

此函数计算UsedRange的第一行数加上UsedRange行数。

1
2
3

Sub test()
MsgBox Worksheets("sheet_name").Range("A65536").End(xlUp).Row
End Sub

由于"A65536"，因此正在列A中寻找值。

第一行将光标移动到该列的最后一个非空行。第二行打印该列行。

1 2	Selection.End(xlDown).Select MsgBox(ActiveCell.Row)

1
2
3
4
5
6
7
8
9
10
11
12
13

Function LastRow(rng As Range) As Long
Dim iRowN As Long
Dim iRowI As Long
Dim iColN As Integer
Dim iColI As Integer
iRowN = 0
iColN = rng.Columns.count
For iColI = 1 To iColN
iRowI = rng.Columns(iColI).Offset(65536 - rng.Row, 0).End(xlUp).Row
If iRowI > iRowN Then iRowN = iRowI
Next
LastRow = iRowN
End Function

数据特定包含找到