Last Row in Range

Question

Having an issue finding information and/or solutions that produce the desired result, so here it goes.

Right now, and potentially at production time, i will have a "template" named-range on a worksheet. On first use this NamedRange is only rngNamed1 but after the first use there could be more Named Ranges similar to this one, say up to rngNamed30.

Lets say on the 2nd run after building up to rngNamed30, that i need to add 3 more of the NamedRange, which results in rngNamed33.

What i need to do is basically find the last row & column of the last Named Range, so i know where to start the copying of data to and declare the next Named Range.

What i have tried so far:

Dim rng As range
Set rng = range("rngNamed1")
'Set rng = rng.Find("*", rng.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False)
Debug.Print rng.Find("*", rng.Cells(1, 1), , , , xlPrevious).Address

The problem with most of the solutions out there is they are based on finding the last cell WITH DATA. I just need to know the Last Cell of the Range, irregardless of whether it contains data or not.

Range("X").Row returns the first row of the range named X. Range("X").Rows.Count returns the number of rows in range X. Put these together and you can calculate the last row of the range. Range("X").Address returns an address string which can be decoded. Would either of these techniques help? — Tony Dallimore
Yeah kinda figured that....was trying to make sure that there wasnt some obscure option in SpecialCells or some other internal method that would provide the information. — GoldBishop

JustinJDavies JustinJDavies · Accepted Answer · 2012-09-25T18:45:48

You can do this easily using the Row and Rows.Count of the range:

Sub NextRowAfterRange()
    Dim ws As Worksheet
    Dim rangeNom As String
    Dim nextRow As Long

    ' Enter desired name here
    rangeNom = "rngName1"

    Set ws = ActiveSheet

    nextRow = ws.Range(rangeNom).Row + ws.Range(rangeNom).Rows.Count

    MsgBox nextRow
End Sub

Last Row in Range

What i have tried so far:

1 Answers