vba 工作表中上次使用的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42078473/
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
Last Used Cell in sheet
提问by Tuckertubaman
I'm new here and I'm looking to use Excel VBA to return the last used cell in a worksheet.
我是新来的，我希望使用 Excel VBA 返回工作表中最后使用的单元格。
I'vv looked at Error in finding last used cell in VBA) but that didn't answer the two problems I have:
我查看了Error infind last used cell in VBA），但这并没有解决我遇到的两个问题：
The
.Cells.Find(...).Rowmethod takes WAY too long in my code.My meaning of "last used cell" might be odd...the cell can be blank. I want to take the column that has the last used cell and pair it with the row that has the last used cell.
该
.Cells.Find(...).Row方法在我的代码中花费的时间太长。我的“最后使用的单元格”的意思可能很奇怪……单元格可以是空白的。我想取具有最后使用的单元格的列，并将其与具有最后使用的单元格的行配对。
To explain: Assume a sheet is empty except for data in A1:C3, D2and B4. (Exhibit 1)
解释一下：假设一张工作表是空的，除了A1:C3、D2和 中的数据B4。(图表 1)
The last cell I'm interested in is D4because I want the last cell in the sheet that includes all data in the sheet.
我感兴趣的最后一个单元格是D4因为我想要包含工作表中所有数据的工作表中的最后一个单元格。
Now that I've explained what I'm looking for, can anyone provide suggestions as to either
既然我已经解释了我要找的东西，任何人都可以提供建议
- how to make cells.find run faster or
- another credible method to find the "last cell" in a worksheet?
- 如何使 cell.find 运行得更快或
- 在工作表中找到“最后一个单元格”的另一种可靠方法？
Thank you!
谢谢！
回答by A.S.H
Did you try this?
你试过这个吗？
Dim r As Range
Set r = Sheet1.UsedRange.SpecialCells(xlCellTypeLastCell)
Debug.Print r.Address
Output for your example:
您的示例的输出：
$D$4
4 美元
The UsedRange is known to not always match the actually useddata range. Some workaround would be to use CurrentRegion:
已知 UsedRange 并不总是与实际使用的数据范围匹配。一些解决方法是使用CurrentRegion：
Dim r As Range
With Sheet1.Range("A1").CurrentRegion
Set r = Sheet1.Cells(.Rows.count, .Columns.count)
End With
Debug.Print r.Address
also if the data does not start at A1, maybe this:
此外，如果数据不是从 开始A1，也许是这样：
With Sheet1.Cells.Find("*").CurrentRegion
回答by brettdj
Use Findboth by row andcolumn to identify this cell.
使用Find按行和按列来标识此单元格。
Sub GetLastCellRange()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious)
If Not rng1 Is Nothing Then
Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column))
MsgBox "Range is " & rng3.Address(0, 0)
'if you need to actual select the range (which is rare in VBA)
Application.Goto rng3
Else
MsgBox "sheet is blank", vbCritical
End If
End Sub
回答by PaulG
Although the answers are very valid, I'd be careful using SpecialCells, CurrentRegion and all those.
尽管答案非常有效，但我会小心使用 SpecialCells、CurrentRegion 和所有这些。
Although they work most of the time, they are not 100% reliable in my experience.
尽管它们大部分时间都在工作，但根据我的经验，它们并不是 100% 可靠的。
For example, CurrentRegion will not pick up all data if the data happens to have an empty column or row etc.
例如，如果数据碰巧有空列或空行等，则 CurrentRegion 不会选取所有数据。
The best Approach, in my opinion is to always use headers with your data. You can then enumerate all headers and find the last used row in that column. You can then determine the greatest row used and now define your data range.
在我看来，最好的方法是始终对数据使用标题。然后，您可以枚举所有标题并找到该列中最后使用的行。然后您可以确定使用的最大行，现在定义您的数据范围。
Btw, if you select the last cell in a specified column then use the Range.End(xlUp) you can quickly determine the last used row for that column without Looping.
顺便说一句，如果您选择指定列中的最后一个单元格，然后使用 Range.End(xlUp) 您可以快速确定该列最后使用的行而无需循环。
