从范围 VBA 中查找最后一个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41363555/
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
Find Last cell from Range VBA
提问by Fajka
How to find location of last cell from defined Range? Cell does not have to contain any data but must be most right and most down located cell from certain Range.
如何从定义的范围中找到最后一个单元格的位置？单元格不必包含任何数据，但必须是特定范围内最右侧和最下方的单元格。
Set rngOrigin = wksOrigin.Cells(IntFirstRow, IntFirstColumn).CurrentRegion
I wish to receive
我希望收到
Cells(i,j)
回答by user3598756
maybe you're after this:
也许你在追求这个：
'absolute indexes from cell A1
With rngOrigin
i = .Rows(.Rows.count).row
j = .Columns(.Columns.count).Column
End With
'relative indexes from rngOrigin upleftmost cell
With rngOrigin
i = .Rows(.Rows.count).row - .Rows(1).row + 1
j = .Columns(.Columns.count).Column - .Columns(1).Column + 1
End With
回答by Jalal
Perhaps this is what you want:
也许这就是你想要的：
Dim rngLastCell As Range
Set rngLastCell = rngOrigin(rngOrigin.Count)
回答by S Meaden
try the following
尝试以下
rngOrigin.End(xlDown).End(xlRight)
or you could use the CurrentRegionand count the rows and columns and use Offset
或者您可以使用CurrentRegion并计算行和列并使用Offset
回答by Shai Rado
In your case, since you want to find the cell to the most right and down in your wksOrigin(defined as Worksheet), you could use the SpecialCells(xlCellTypeLastCell)to get the last cell Row and Column.
在您的情况下，由于您想在您的wksOrigin（定义为工作表）中找到最右侧和最下方的单元格，您可以使用SpecialCells(xlCellTypeLastCell)来获取最后一个单元格的行和列。
i = wksOrigin.Cells.SpecialCells(xlCellTypeLastCell).Row ' <-- get last row number
j = wksOrigin.Cells.SpecialCells(xlCellTypeLastCell).Column ' <-- get last column number
If you want to debug your result, you can add:
如果要调试结果，可以添加：
MsgBox "Last row at " & i & ", last column at " & j
回答by Fajka
I handled it in below code but your remarks were helpful. Thank you.
我在下面的代码中处理了它，但你的评论很有帮助。谢谢你。
intLastRow = rngOrigin.Cells(1, 1).Row + rngOrigin.Rows.Count - 1
intLastCol = rngOrigin.Cells(1, 1).Column + rngOrigin.Columns.Count - 1
回答by Shashiraju
Use this to code find the last cell in a given range
使用此代码查找给定范围内的最后一个单元格
Sub GetLastCellFromRange()
Dim rng As Range
Set rng = Range("$C:$E")
'Set rng = Range(Selection.Address) ' Use this line to select the range in worksheet
MsgBox "Last Cell of given range is : " & rng.Cells(rng.Rows.Count, rng.Columns.Count).Address
End Sub
I hope it will help you
我希望它会帮助你
回答by DB user10082797
The answers given by others mostly work, but not if the region is a union of non-contiguous cells. Here is a version that works consistently for single and multi-area regions, contiguous and non-contiguous.
其他人给出的答案大多有效，但如果该区域是非连续细胞的联合，则无效。这是一个适用于单一和多区域区域、连续和非连续区域的版本。
Function LastCellOfRange(rng As Excel.Range) As Excel.Range
Dim area As Excel.Range
Dim rowNum As Long
Dim maxRow As Long
Dim colNum As Long
Dim maxCol As Long
Dim areaIdx As Integer
Set LastCellOfRange = Nothing
maxRow = 0
maxCol = 0
For areaIdx = 1 To rng.Areas.Count
Set area = rng.Areas(areaIdx)
rowNum = area.Cells(area.Cells.Count).row
If (rowNum > maxRow) Then
maxRow = rowNum
End If
colNum = area.Cells(area.Cells.Count).Column
If (colNum > maxCol) Then
maxCol = colNum
End If
Next areaIdx
Set LastCellOfRange = rng.Worksheet.Cells(maxRow, maxCol)
Set area = Nothing
End Function
回答by Nelson Yang
If you want the absolute last cell of a defined range, regardless of whether it has any content, here is a simple solution
如果你想要一个定义范围的绝对最后一个单元格，不管它是否有任何内容，这里有一个简单的解决方案
Dim InputRng As Range 'define a range for the test'
Set InputRng = Range("$F:$F")
MsgBox InputRng(1).Address & ":" & InputRng(InputRng.Cells.Count).Address 'This would output the absolute address of defined range'
