how to tell if a named range exists

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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"
 
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"
 
Back
Top