Set rngOrigin = wksOrigin.Cells(IntFirstRow, IntFirstColumn).CurrentRegion
I wish to receive
Cells(i,j)
Dim rngLastCell As Range
Set rngLastCell = rngOrigin(rngOrigin.Count)
worksheets(1).range("1:3").count代替worksheets(1).rows("1:3").count，或者使用他建议的anyRange.Cells(anyRange.Cells.Count)。对于非连续范围，上述解决方案将无法工作！另一个代码应该检查给定范围中最大的列和最大的行号。要检查给定范围是否为非连续范围，请使用If rngOrigin.Areas.Count > 1 Then '...。 - Jalal'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
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
intLastRow = rngOrigin.Cells(1, 1).Row + rngOrigin.Rows.Count - 1
intLastCol = rngOrigin.Cells(1, 1).Column + rngOrigin.Columns.Count - 1
rngOrigin并使用（对我来说）更合适的范围规定。 - user3598756rngOrigin.End(xlDown).End(xlRight)
或者您可以使用CurrentRegion，计算行数和列数，并使用Offset
另外，您还可以使用这种结构，即使基于整个行或整个列的范围也可以使用。
Sub Test()
Dim rngOrigin As Excel.Range
Set rngOrigin = Range("$A$1:$D$6")
Dim rngLast As Excel.Range
Set rngLast = rngOrigin.Cells(rngOrigin.Cells.Count)
Debug.Print rngLast.Address
End Sub
Sub Test()
Dim rngOrigin As Excel.Range
Set rngOrigin = Range("$A$1:$D$6,$F$1:$G$6")
Debug.Print rngOrigin.Areas(1).Cells(rngOrigin.Areas(1).Cells.Count).Address
Debug.Print rngOrigin.Areas(2).Cells(rngOrigin.Areas(2).Cells.Count).Address
End Sub
这是用于非连续范围的
`Sub GetLastCellFromRange() Dim rng As Range
Set rng = Range(Range("$C$10:$E$20"), Range("$G$10:$H$50"))
'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
结束子程序
Sub GetLastCellFromRange()
Dim rng As Range
Set rng = Range("$C$10:$E$20")
'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
我希望它能对你有所帮助
Set rng = Range(Range("$C$10:$E$20"), Range("$G$10:$H$50"))
' 可使用如下代码在工作表中选择范围
' Set rng = Range(Selection.Address)
MsgBox "给定范围的最后一个单元格为：" & rng.Cells(rng.Rows.Count, rng.Columns.Count).AddressEnd Sub - Shashiraju许多答案在给定范围连续的情况下都可以工作。这是我会用于您绝对确定将是连续的范围：
Sub test()
Dim myRng As Range, lastCell As Range
Set myRng = Range("A1:D4")
Set lastCell = myRng.Cells(myRng.Rows.Count, myRng.Columns.Count)
Debug.Print lastCell.Address 'returns $D$4
End Sub
对于非连续的情况，DB用户10082797提供了一个很好的解决方案，但是当范围被对角线上升放置时，他们的函数会失败（例如，如果您传递rng=A3:B4,C1:D2，则输出为D4，这不是原始范围的一部分。）
因此问题变成了，范围A3:B4,C1:D2中的最后一个单元格是什么？是B4还是D2？这是程序员的决定。这是我根据DB用户10082797的函数编写的一个函数：
Function LastCellOfRange(rng As Range, Optional returnLastRow As Boolean = True) As Range
'returns the last cell in @rng.
'if @returnLastRow is TRUE, then the output will always be in the right most cell of the last row of @rng
'if @returnLastRow is FALSE, then the output will always be in the bottom most cell of the last column of @rng
'(@returnLastRow only matters for non-contiguous ranges under certain circumstances.)
'initialize variables
Dim area As Range, areaIdx As Long
Dim lastCellInArea As Range
'loop thru each area in the selection
For areaIdx = 1 To rng.Areas.Count
Set area = rng.Areas(areaIdx) 'get next area
Set lastCellInArea = area.Cells(area.Rows.Count, area.Columns.Count) 'get the last cell in the area
'if:
' the return is empty
' OR if the last row needs to be returned and this row is larger than the last area's
' OR if the last row needs to be returned and this row is the same as the last area's but has a larger column
' OR if the last column needs to be returned and this column is larger than the last area's
' OR if the last column needs to be returned and this column is the same as the last area's but has a larger row
'THEN:
' make this cell the return range
If LastCellOfRange Is Nothing Then
Set LastCellOfRange = lastCellInArea '(must be seperate from the other statment when its not set to anything)
ElseIf _
returnLastRow = True And lastCellInArea.Row > LastCellOfRange.Row _
Or returnLastRow = True And lastCellInArea.Row = LastCellOfRange.Row And lastCellInArea.Column > LastCellOfRange.Column _
Or returnLastRow = False And lastCellInArea.Column > LastCellOfRange.Column _
Or returnLastRow = False And lastCellInArea.Column = LastCellOfRange.Column And lastCellInArea.Row > LastCellOfRange.Row _
Then
Set LastCellOfRange = lastCellInArea
End If
Next areaIdx
End Function
Sub test()
Dim myRng As Range
Set myRng = Range("A3:B4,C1:D2")
Debug.Print LastCellOfRange(myRng).Address 'returns $B$4
Debug.Print LastCellOfRange(myRng, False).Address 'returns $D$2
End Sub
Dim InputRng As Range 'define a range for the test'
Set InputRng = Range("$F$3:$F$15")
MsgBox InputRng(1).Address & ":" & InputRng(InputRng.Cells.Count).Address 'This would output the absolute address of defined range'
wksOrigin（定义为工作表）中最右边和最下面的单元格，因此您可以使用SpecialCells(xlCellTypeLastCell)来获取最后一个单元格的行和列。i = wksOrigin.Cells.SpecialCells(xlCellTypeLastCell).Row ' <-- get last row number
j = wksOrigin.Cells.SpecialCells(xlCellTypeLastCell).Column ' <-- get last column number
MsgBox "Last row at " & i & ", last column at " & j