hi all,

in vba - how do you tell if a named range exists?

and whats the easiest way to create a named range

now i am doing:

Dim l As Range
ActiveWorkbook.Names("LastUsed").Delete
l = .Cells(Cells.Rows.Count, "C").End(xlUp)(2)
ActiveWorkbook.Names.Add name:="LastUsed", _
RefersToR1C1:="='Customer Orders'!R" & l.Row & "C" & l.Column

is there a better way to do this?

tia

J

Dim nme As Name

On Error Resume Next
Set nme = ActiveWorkbook.Names("holidays")
On Error GoTo 0
If Not nme Is Nothing Then
MsgBox "Name exists"
End If


Set l = .Cells(Cells.Rows.Count, "C").End(xlUp)(2)
Range("'Customer Orders'!" & l.Address).Name = "LastUsed"


--
HTH

Bob Phillips

"Gixxer_J_97" wrote in message
...
hi all,

in vba - how do you tell if a named range exists?

and whats the easiest way to create a named range

now i am doing:

Dim l As Range
ActiveWorkbook.Names("LastUsed").Delete
l = .Cells(Cells.Rows.Count, "C").End(xlUp)(2)
ActiveWorkbook.Names.Add name:="LastUsed", _
RefersToR1C1:="='Customer Orders'!R" & l.Row & "C" & l.Column

is there a better way to do this?

tia

J

There is no reason to tell if it exists if you want to define it. If it
already exists, it will be replace. If you want to see if it exists before
using it

On error resume Next
set rng = Range("LastUsed")
On Error goto 0
if rng is nothing then
.Cells(rows.count,3).End(xlup)(2).Name = "LastUsed"
End if

but if that is the only reason to test, then just do

.Cells(rows.count,3).End(xlup)(2).Name = "LastUsed"

--
Regards,
Tom Ogilvy



"Gixxer_J_97" wrote in message
...
hi all,

in vba - how do you tell if a named range exists?

and whats the easiest way to create a named range

now i am doing:

Dim l As Range
ActiveWorkbook.Names("LastUsed").Delete
l = .Cells(Cells.Rows.Count, "C").End(xlUp)(2)
ActiveWorkbook.Names.Add name:="LastUsed", _
RefersToR1C1:="='Customer Orders'!R" & l.Row & "C" & l.Column

is there a better way to do this?

tia

J