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


"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

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

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

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" <(E-Mail Removed)> wrote in message
news:B0BA3020-CFC6-44B4-88B3-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:B0BA3020-CFC6-44B4-88B3-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:B0BA3020-CFC6-44B4-88B3-(E-Mail Removed)...
> > 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
>

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

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" <(E-Mail Removed)> wrote in message
news:4B284942-42A6-4C75-8DEF-(E-Mail Removed)...
> 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

Yay it works!
Thanks you guys are great!

"Rick Rothstein" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:B0BA3020-CFC6-44B4-88B3-(E-Mail Removed)...
> > 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
>
> .
>