Excel VBA：获取包含选定范围内数据的最后一个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8785063/
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
Excel VBA: Get Last Cell Containing Data within Selected Range
提问by sikas
How do I use Excel VBA to get the last cell that contains data within a specific range, such as in columns A and B Range("A:B")?
如何使用 Excel VBA 获取包含特定范围内数据的最后一个单元格，例如 A 列和 B 列Range("A:B")？
回答by brettdj
using Findlike below is useful as it
使用Find如下所示很有用，因为它
- can find the last (or first) cell in a 2D range immediately
- testing for
Nothingidentifies a blank range - will work on a range that may not be contiguous (ie a
SpecialCellsrange)
- 可以立即找到二维范围内的最后一个（或第一个）单元格
- 测试
Nothing确定一个空白范围 - 将适用于可能不连续的
SpecialCells范围（即范围）
change "YourSheet"to the name of the sheet you are searching
更改"YourSheet"为您正在搜索的工作表的名称
Sub Method2()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("YourSheet")
Set rng1 = ws.Columns("A:B").Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then
MsgBox "last cell is " & rng1.Address(0, 0)
Else
MsgBox ws.Name & " columns A:B are empty", vbCritical
End If
End Sub
回答by JMax
You can try several ways:
您可以尝试几种方法：
Using xlUp
使用 xlUp
Dim WS As Worksheet
Dim LastCellA As Range, LastCellB As Range
Dim LastCellRowNumber As Long
Set WS = Worksheets("Sheet1")
With WS
Set LastCellA = .Cells(.Rows.Count, "A").End(xlUp)
Set LastCellB = .Cells(.Rows.Count, "B").End(xlUp)
LastCellRowNumber = Application.WorksheetFunction.Max(LastCellA.Row, LastCellB.Row)
End With
Using SpecialCells
使用特殊单元格
Dim WS As Worksheet
Dim LastCell As Range
Set LastCell = Range("A:B").SpecialCells(xlCellTypeLastCell)
The latter can sometimes be tricky and might not work as you wanted it to.
后者有时可能很棘手，可能无法如您所愿。
More tips
更多提示
You can also have a look at Chip Pearson's page about this issue
您还可以查看Chip Pearson 关于此问题的页面
回答by DKSan
For a variable selection you can use
对于变量选择，您可以使用
Sub test()
Dim arrArray As Variant
Dim iAct As Integer
Dim iHighest As Integer
arrArray = Split(Selection.Address(1, 1, xlR1C1), ":")
For Count = Right(arrArray(0), 1) To Right(arrArray(1), 1)
iAct = ActiveSheet.Cells(Rows.Count, Count).End(xlUp).Row
If iAct > iHighest Then iHighest = iAct
Next Count
MsgBox iHighest
End Sub
