vba 如何确定VBA中使用的最后一行，包括其间的空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13686801/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to determine the last Row used in VBA including blank spaces in between
提问by Jonathan Raul Tapia Lopez
How can I determine the last row in an Excel sheet, including some blank lines in the middle?
如何确定 Excel 工作表中的最后一行，包括中间的一些空行？
With this function:
有了这个功能：
Function ultimaFilaBlanco(col As String) As Long
Dim lastRow As Long
With ActiveSheet
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, col).End(xlUp).row
End With
ultimaFilaBlanco = lastRow
End Function
And this data:
而这个数据：
Row 1 : Value
Row 2 : Value
Row 3 : Value
Row 4 : Value
Row 5 : Value
Row 6 : White
Row 7 : Value
Row 8 : Value
Row 9 : Value
Row 10 : White
Row 11 : White
Row 12 : White
Row 13 : Value
Row 14 : White
The function returns 5, and I need 13. Any idea how to do it?
该函数返回 5，而我需要 13。知道怎么做吗？
采纳答案by fbonetti
You're really close. Try using a large row number with End(xlUp)
你真的很亲近。尝试使用大行号End(xlUp)
Function ultimaFilaBlanco(col As String) As Long
Dim lastRow As Long
With ActiveSheet
lastRow = ActiveSheet.Cells(1048576, col).End(xlUp).row
End With
ultimaFilaBlanco = lastRow
End Function
回答by Abdelhameed Mahmoud
The best way to get number of last row used is:
获取使用的最后一行数的最佳方法是：
ActiveSheet.UsedRange.Rows.Countfor the current sheet.
ActiveSheet.UsedRange.Rows.Count对于当前工作表。
Worksheets("Sheet name").UsedRange.Rows.Countfor a specific sheet.
Worksheets("Sheet name").UsedRange.Rows.Count对于特定的工作表。
To get number of the last column used:
要获取使用的最后一列的编号：
ActiveSheet.UsedRange.Columns.Countfor the current sheet.
ActiveSheet.UsedRange.Columns.Count对于当前工作表。
Worksheets("Sheet name").UsedRange.Columns.Countfor a specific sheet.
Worksheets("Sheet name").UsedRange.Columns.Count对于特定的工作表。
I hope this helps.
我希望这有帮助。
Abdo
阿卜杜
回答by manuel
ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).Row -1
Short. Safe. Fast. Will return the last non-empty row even if there are blank lines on top of the sheet, or anywhere else. Works also for an empty sheet (Excel reports 1 used row on an empty sheet so the expression will be 1). Tested and working on Excel 2002 and Excel 2010.
短的。安全的。快速地。即使工作表顶部或其他任何地方有空行，也将返回最后一个非空行。也适用于空工作表（Excel 报告 1 在空工作表上使用了行，因此表达式将为 1）。在 Excel 2002 和 Excel 2010 上测试和工作。
回答by Sid Holland
I use the following:
我使用以下内容：
lastrow = ActiveSheet.Columns("A").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
It'll find the last row in a specific column. If you want the last used row for anycolumn then:
它将在特定列中找到最后一行。如果您想要任何列的最后使用的行，则：
lastrow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
回答by jtorreon
LastRow = ActiveSheet.UsedRange.Rows.Count
回答by user1965813
The Problem is the "as string" in your function. Replace it with "as double" or "as long" to make it work. This way it even works if the last row is bigger than the "large number" proposed in the accepted answer.
问题是函数中的“as string”。用“双倍”或“一样长”替换它以使其工作。这样，如果最后一行大于接受的答案中提出的“大数字”，它甚至可以工作。
So this should work
所以这应该有效
Function ultimaFilaBlanco(col As double) As Long
Dim lastRow As Long
With ActiveSheet
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, col).End(xlUp).row
End With
ultimaFilaBlanco = lastRow
End Function
回答by jainashish
Well apart from all mentioned ones, there are several other ways to find the last row or column in a worksheet or specified range.
除了所有提到的方法之外，还有其他几种方法可以找到工作表或指定范围中的最后一行或最后一列。
Function FindingLastRow(col As String) As Long
'PURPOSE: Various ways to find the last row in a column or a range
'ASSUMPTION: col is passed as column header name in string data type i.e. "B", "AZ" etc.
Dim wks As Worksheet
Dim lstRow As Long
Set wks = ThisWorkbook.Worksheets("Sheet1") 'Please change the sheet name
'Set wks = ActiveSheet 'or for your problem uncomment this line
'Method #1: By Finding Last used cell in the worksheet
lstRow = wks.Range("A1").SpecialCells(xlCellTypeLastCell).Row
'Method #2: Using Table Range
lstRow = wks.ListObjects("Table1").Range.Rows.Count
'Method #3 : Manual way of selecting last Row : Ctrl + Shift + End
lstRow = wks.Cells(wks.Rows.Count, col).End(xlUp).Row
'Method #4: By using UsedRange
wks.UsedRange 'Refresh UsedRange
lstRow = wks.UsedRange.Rows(wks.UsedRange.Rows.Count).Row
'Method #5: Using Named Range
lstRow = wks.Range("MyNamedRange").Rows.Count
'Method #6: Ctrl + Shift + Down (Range should be the first cell in data set)
lstRow = wks.Range("A1").CurrentRegion.Rows.Count
'Method #7: Using Range.Find method
lstRow = wks.Column(col).Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
FindingLastRow = lstRow
End Function
Note: Please use only one of the above method as it justifies your problem statement.
注意：请仅使用上述方法之一，因为它可以证明您的问题陈述是正确的。
Please pay attention to the fact that Find method does not see cell formattingbut only data, hence look for xlCellTypeLastCell if only data is important and not formatting. Also, merged cells (which must be avoided) might give you unexpected resultsas it will give you the row number of the first cell and not the last cell in the merged cells.
请注意Find 方法看不到单元格格式而只看到数据这一事实，因此如果只有数据重要而不是格式，则查找 xlCellTypeLastCell。此外，合并单元格（必须避免）可能会给您带来意想不到的结果，因为它会给您第一个单元格的行号，而不是合并单元格中的最后一个单元格。
回答by JHK
If sheet contains unused area on the top, UsedRange.Rows.Countis not the maximum row.
如果工作表顶部包含未使用的区域，UsedRange.Rows.Count则不是最大行。
This is the correct max row number.
这是正确的最大行数。
maxrow = Sheets("..name..").UsedRange.Rows(Sheets("..name..").UsedRange.Rows.Count).Row
回答by 18C
ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.count).row
ActiveSheet can be replaced with WorkSheets(1)or WorkSheets("name here")
ActiveSheet 可以替换为WorkSheets(1)或WorkSheets("name here")
回答by Odiljon Jakhangirov
here is my code to find the next empty row for example in first row 'A'. To use it for any other row just change cells(i,2 or 3 or 4 so on)
这是我的代码，用于查找下一个空行，例如在第一行“A”中。要将其用于任何其他行，只需更改单元格（i、2 或 3 或 4 等）
Sheets("Sheeet1").Select
for i=1 to 5000
if cells(i,1)="" then nextEmpty=i goto 20
next i
20 'continue your code here
enter code here
