Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Worksheet exists, delete it, then re-add it | Excel Programming | |||
if workbooks exists delete | Excel Programming | |||
If Pivot Table Exists, Delete on Close | Excel Programming | |||
If sheet exists, delete | Excel Programming | |||
If worksheet exists delete | Excel Programming |