Trouble Naming Range

[Michael Smith]

Having trouble naming a range...

Dim rng as Range
Set Rng.....

I need rng to be the length of my sheet...trying to name it and its
failing on me.

 

[Ron de Bruin]

Hi

Do you mean this(example for the A column)

Sub test()
Dim rng As Range
Set rng = Sheets("sheet1").Range("A1:A" & Sheets(1).Range("A" & Rows.Count).End(xlUp).Row)
MsgBox rng.Address
End Sub

 

[Tom Ogilvy]

Dim rng as Range, i as Long
' set i to the column you need
i = 3
set rng = Range(cells(1,i),cells(rows.count,i).End(xlup))
rng.Name = "MyName"

msgbox Range("MyName").Address(external:=True)
msgbox Thisworkbook.Names("MyName") _
.RefersToRange.Address(external:=True)

 

[Michael Smith]

that was it! Thanks mucho