DELETE A RANGE NAME IF EXISTS

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Hi
I have a macro that imports data from a text file and creates a new range
name every time.
fnd_gfm_1
I need some code to delete this range name if it exists.
Note that the 1 can change depending on how many times the macro has run, so
the code needs to delete fnd_gfm_*

Any help is much appreciated
Thanks
Simon
 
Dim myName as String

For Each myName In ActiveWorkbook.Names
If Left(myName.Name, 8) = "fnd_gfm_" Then
ActiveWorkbook.Names(myName).Delete
End if
Next

adjust as needed if not active workbook...
 
I just stole my earlier snippet from something I had working in a particular
situation that suited my own purpose. But after looking a little closer, it
would be better and more flexible like this...

Dim myName as Name
Dim wb as Workbook
Set wb = Workbooks("WorkbookToLookIn.xls") '(or whatever yours is named)

For Each myName In wb.Names
If Left(myName.Name, 8) = "fnd_gfm_" Then
MyName.Delete
End if
Next
 
Thanks Lynn
I have tried this but it doesnt seem to work.
The code runs OK, but names are still there.
 
Step through this code with F8 and hover over "nName.Name" to ensure the
names matches the pattern


Sub DeleteNames()
Dim nName As Name

For Each nName In ThisWorkbook.Name
If nName.Name Like "fnd_gfm*" _
Then nName.Delete
Next nName
End Sub
 
Are your actual range Names all in lower case as you showed? The tests Simon
(and Dave) posted are case sensitive tests. These can be made case
insensitive if you need them to be, but I thought I would just check first.
 
Still not working for me.
ThisWorkbook.Name created a compiler error so I changed to .Names, is that
correct?

Hovering over shows nName.Name ="_xlfn.IFERROR"

The range names are lower case and I am using v2007.

Any thoughts
Thanks in advance
Simon
 
When I run further through it, hovering over shows
nName.Name="Report!fnd_gfm_11"
I tried editing the code to include the worksheeet name Report! but the
range names are still there.
 
Give this modification of Dave's (ozgrid.com) code a try...

Sub DeleteNames()
Dim nName As Name
For Each nName In ThisWorkbook.Name
If nName.Name Like "*fnd_gfm*" Then nName.Delete
Next nName
End Sub
 
Yay it works!
Thanks you guys are great!

Rick Rothstein said:
Are your actual range Names all in lower case as you showed? The tests Simon
(and Dave) posted are case sensitive tests. These can be made case
insensitive if you need them to be, but I thought I would just check first.

--
Rick (MVP - Excel)





.
Click to expand...
 
Back
Top