Code to name a range ....

Posted by Paul Magruder on April 23, 2001 6:41 AM

I need to Name a range that includes the last 14 cells that have data in them, in a column.
Selection.End(xlDown).select gets me to the last data point in the column. How do I tell excel to select the Activecell, and the previous 13 cells, so I can give them a range name?

Thanks in Advance
Paul

Posted by Dave Hawley on April 23, 2001 7:27 AM

Hi Paul

Try this.

Sub NameLast14()
Dim RCellTop As Range
Dim RCellBot As Range

Set RCellTop = Selection.End(xlDown).Offset(-14, 0)
Set RCellBot = Selection.End(xlDown)

Range(RCellTop, RCellBot).Name = "MyRange"

Set RCellTop = Nothing
Set RCellBot = Nothing

End Sub

Dave

OzGrid Business Applications