DELETE A RANGE NAME IF EXISTS
 

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

DELETE A RANGE NAME IF EXISTS
 

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...


"Simon" wrote:

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

DELETE A RANGE NAME IF EXISTS
 

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



"B Lynn B" wrote:

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...


"Simon" wrote:

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

DELETE A RANGE NAME IF EXISTS
 

Thanks Lynn
I have tried this but it doesnt seem to work.
The code runs OK, but names are still there.

"B Lynn B" wrote:

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



"B Lynn B" wrote:

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...


"Simon" wrote:

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

DELETE A RANGE NAME IF EXISTS
 

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



--
Regards
Dave Hawley
www.ozgrid.com
"Simon" wrote in message
...
Thanks Lynn
I have tried this but it doesnt seem to work.
The code runs OK, but names are still there.

"B Lynn B" wrote:

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



"B Lynn B" wrote:

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...


"Simon" wrote:

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

DELETE A RANGE NAME IF EXISTS
 

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)



"Simon" wrote in message
...
Thanks Lynn
I have tried this but it doesnt seem to work.
The code runs OK, but names are still there.

"B Lynn B" wrote:

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



"B Lynn B" wrote:

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...


"Simon" wrote:

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

DELETE A RANGE NAME IF EXISTS
 

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

"ozgrid.com" wrote:

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



--
Regards
Dave Hawley
www.ozgrid.com
"Simon" wrote in message
...
Thanks Lynn
I have tried this but it doesnt seem to work.
The code runs OK, but names are still there.

"B Lynn B" wrote:

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



"B Lynn B" wrote:

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...


"Simon" wrote:

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

DELETE A RANGE NAME IF EXISTS
 

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.


"Simon" wrote:

Thanks Lynn
I have tried this but it doesnt seem to work.
The code runs OK, but names are still there.

"B Lynn B" wrote:

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



"B Lynn B" wrote:

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...


"Simon" wrote:

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

DELETE A RANGE NAME IF EXISTS
 

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

--
Rick (MVP - Excel)



"Simon" wrote in message
...
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.


"Simon" wrote:

Thanks Lynn
I have tried this but it doesnt seem to work.
The code runs OK, but names are still there.

"B Lynn B" wrote:

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



"B Lynn B" wrote:

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...


"Simon" wrote:

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