Last Row In Named Range

[Guest]

Can someone tell me how to return the last row in a named range?

Thank you.
Sprinks

 

[Guest]

Try this
lastRow = Cells(Rows.Count, NamedRange.Columns(1)).End(xlUp).Row

 

[Gary Keramidas]

try this

Range("test").SpecialCells(xlCellTypeLastCell)

 

[Gary Keramidas]

sorry, you wanted the row, not the cell
i = Range("test").SpecialCells(xlCellTypeLastCell).Row

 

[Guest]

JLGWhiz,

Thanks.

Try this
lastRow = Cells(Rows.Count, NamedRange.Columns(1)).End(xlUp).Row

 

[Rick Rothstein \(MVP - VB\)]

Can someone tell me how to return the last row in a named range?

LastRow = Range("YourRangeName").Row + Range("YourRangeName").Count - 1

or, more compactly....

With Range("MyRange")
LastRow = .Row + .Count - 1
End With


Rick

 

[Dave Peterson]

One more (useful if there are multiple areas in the range):

Option Explicit
Sub testme()

Dim myRng As Range
Set myRng = Worksheets("Sheet1").Range("myname")

With myRng
With .Areas(.Areas.Count)
MsgBox .Rows(.Rows.Count).Row
End With
End With

End Sub

 

[Peter T]

Just to add, if not certain the 'last' area contains the 'lowest' row, would
need to loop each area.

Regards,
Peter T