Find Last cell from Range VBA

Question

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)

Answer 1

Perhaps this is what you want:

Dim rngLastCell As Range
Set rngLastCell = rngOrigin(rngOrigin.Count)

Answer 2

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

Answer 3

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

Answer 4

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.

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

Answer 5

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

Answer 6

Use this to code find the last cell in a given range

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

I hope it will help you

Answer 7

you could try the following but it relies upon cells always being populated

rngOrigin.End(xlDown).End(xlRight)

or you could use the CurrentRegion and count the rows and columns and use Offset

Alternatively, you could use this construct which works even with ranges based on entire rows or entire columns.

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

Finally, for ranges with multiple areas you'll have to script against a range's Areas collection ...

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

Answer 8

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$3:$F$15") 
MsgBox InputRng(1).Address & ":" & InputRng(InputRng.Cells.Count).Address    'This would output the absolute address of defined range'