Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete names except Print_Area
I have a macro that deletes all of the named ranges in my workbook. The
problem is that it also deletes the "Print_Area" range, which results in resetting my print area. Is there a way to modify this so that it will keep my old print area? Sub DeleteNames() ' ' Gets rid of all named ranges ' For Each nName In Names nName.Delete Next ' End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete names except Print_Area
Sub DeleteNames()
' ' Gets rid of all named ranges except Print_Area ' For Each nName In Names if nName.Name < "Print_area" then nName.Delete end if Next ' End Sub HTH '-- AP a écrit dans le message de ups.com... I have a macro that deletes all of the named ranges in my workbook. The problem is that it also deletes the "Print_Area" range, which results in resetting my print area. Is there a way to modify this so that it will keep my old print area? Sub DeleteNames() ' ' Gets rid of all named ranges ' For Each nName In Names nName.Delete Next ' End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete names except Print_Area
Dear Barber,
Have a go with the code below. The Print_Range name gets prefixed with the sheet it applies to so you need to use the 'Right' function to check the last part of the returned string. So what it's saying is if the last 10 characters of each name do not end in "Print_Area" then delete them. (You can delete the Debug.Print... line which just shows you what's going on in the Intermediate window of the VBE.) Best regards John Sub DeleteNames() ' Gets rid of all named ranges Dim nName As Name For Each nName In Names Debug.Print nName.Name If Right(nName.Name, 10) < "Print_Area" Then nName.Delete End If Next nName End Sub wrote in message ups.com... I have a macro that deletes all of the named ranges in my workbook. The problem is that it also deletes the "Print_Area" range, which results in resetting my print area. Is there a way to modify this so that it will keep my old print area? Sub DeleteNames() ' ' Gets rid of all named ranges ' For Each nName In Names nName.Delete Next ' End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete names except Print_Area
Sub DeleteNames()
' ' Gets rid of all named ranges ' For Each nName In Names if instr(1,nName,Name,"Print_Area",vbtextcompare) = 0 then nName.Delete end if Next ' End Sub -- Regards, Tom Ogilvy " wrote: I have a macro that deletes all of the named ranges in my workbook. The problem is that it also deletes the "Print_Area" range, which results in resetting my print area. Is there a way to modify this so that it will keep my old print area? Sub DeleteNames() ' ' Gets rid of all named ranges ' For Each nName In Names nName.Delete Next ' End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete names except Print_Area
Typo alert patrol:
If InStr(1, nName.Name, "Print_Area", vbTextCompare) = 0 Then (dot instead of a comma in nName.Name) Tom Ogilvy wrote: Sub DeleteNames() ' ' Gets rid of all named ranges ' For Each nName In Names if instr(1,nName,Name,"Print_Area",vbtextcompare) = 0 then nName.Delete end if Next ' End Sub -- Regards, Tom Ogilvy " wrote: I have a macro that deletes all of the named ranges in my workbook. The problem is that it also deletes the "Print_Area" range, which results in resetting my print area. Is there a way to modify this so that it will keep my old print area? Sub DeleteNames() ' ' Gets rid of all named ranges ' For Each nName In Names nName.Delete Next ' End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete names except Print_Area
Be careful.
Excel creates names that it uses (without your knowledge and without your permission). If you delete those names, you could be breaking something that excel needs. If I were you, I'd get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp You can delete just the names you want pretty easily. wrote: I have a macro that deletes all of the named ranges in my workbook. The problem is that it also deletes the "Print_Area" range, which results in resetting my print area. Is there a way to modify this so that it will keep my old print area? Sub DeleteNames() ' ' Gets rid of all named ranges ' For Each nName In Names nName.Delete Next ' End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete names except Print_Area
Thanks for your input guys. Both John's and Tom's macros worked great
for what i need. And thanks Dave for the heads up on the excel-created named ranges and the Name Manager add-in. While the Name Manager wasn't what i was needing for this task, it is a valuable add-in. I was able to clear up some redundant local names as well as get rid of some unused named ranges. And i'm sure i'll find a use for it in the future as well. Thanks again for all of your help. You guys are amazing. Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to delete Macro names | Excel Discussion (Misc queries) | |||
Addin macro to delete names in current workbook | Excel Programming | |||
Deleting all Range Names except Print_Area? | Excel Programming | |||
Macro to delete Defined Names | Excel Programming | |||
macro to change the names and delete closed books | Excel Discussion (Misc queries) |