clear and remove all ranges and names

  • Thread starter Thread starter sophic
  • Start date Start date
S

sophic

I have tried and tried to write some vba to select each range name
clear the contents and remove the name. I know that it should g
something like;

Dim rName As Name
For Each rName In ActiveWorkbook.Names
Application.Goto Reference:= rName
Selection.Clear
Selection.QueryTable.Delete
rName.Delete
Next rName

The problem is in line 3 where I cannot goto a range name specified b
a variable. I have tried things like Range(rName).Select etc but I a
running out of ideas!

Please help soon!!!!

Thanks in advance.

Steve
 
sophic,

If you don't need to select each range prior to deleting it I foun
that by altering your code I got it to work - see below


Sub Delete_Range_Names()

Dim rName As Name
For Each rName In ActiveWorkbook.Names
rName.Delete
Next rName

End Sub


Apologies if I've misunderstood what you were asking

Regards

Seamu
 
Hi
without testing try replacing
Application.Goto Reference:= rName

with
Application.Goto Reference:= range(rName)
 
Steve

For Each rName in ActiveWorkbook.Names
With rNameRefersToRange
.Clear
.Querytable.Delete
End With
rName.Delete
Next rName

Not tested, but should work.
 
With rNameRefersToRange

should be

With rNames.RefersToRange

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Dick Kusleika said:
Steve

For Each rName in ActiveWorkbook.Names
With rNameRefersToRange
.Clear
.Querytable.Delete
End With
rName.Delete
Next rName

Not tested, but should work.
Click to expand...
 
Back
Top