how to tell if a named range exists

[Guest]

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

 

[Bob Phillips]

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"

 

[Tom Ogilvy]

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"