Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I delete all named ranges in a workbook or worksheet?
I have 5-15 sheets in a workbook and I want to delete all the names (25 or
so) in each sheet in the workbook. I have not figured out a way to do this reliably yet with a macro. Then I need to do it again on the next workbook. Suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I delete all named ranges in a workbook or worksheet?
Sub test()
Dim ws As Worksheet Dim nm As Name For Each ws In Worksheets For Each nm In ws.Names nm.Delete Next nm Next ws End Sub "AZSteve" wrote: I have 5-15 sheets in a workbook and I want to delete all the names (25 or so) in each sheet in the workbook. I have not figured out a way to do this reliably yet with a macro. Then I need to do it again on the next workbook. Suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I delete all named ranges in a workbook or worksheet?
Try the below
Sub DeleteNames() Dim nmRange As Name For Each nmRange In ActiveWorkbook.Names nmRange.Delete Next End Sub If this post helps click Yes --------------- Jacob Skaria "AZSteve" wrote: I have 5-15 sheets in a workbook and I want to delete all the names (25 or so) in each sheet in the workbook. I have not figured out a way to do this reliably yet with a macro. Then I need to do it again on the next workbook. Suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I delete all named ranges in a workbook or worksheet?
There appear to be certain range names that you should not delete, so here
is a macro that preserves them if they are present and deletes all the rest... Sub DeleteNames() ' First seen posted by Bob Phillips Dim N As Name For Each N In ActiveWorkbook.Names If N.Name Like "*_FilterDatabase" Or _ N.Name Like "*Print_Area" Or _ N.Name Like "*Print_Titles" Or _ N.Name Like "*wvu.*" Or _ N.Name Like "*wrn.*" Or _ N.Name Like "*!Criteria" Then Else N.Delete End If Next N End Sub -- Rick (MVP - Excel) "AZSteve" wrote in message ... I have 5-15 sheets in a workbook and I want to delete all the names (25 or so) in each sheet in the workbook. I have not figured out a way to do this reliably yet with a macro. Then I need to do it again on the next workbook. Suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I delete all named ranges in a workbook or worksheet?
I want to delete all names, so the other posts gave me that answer. However
I will save yours for when I want to keep certain names. Thanks. "Rick Rothstein" wrote: There appear to be certain range names that you should not delete, so here is a macro that preserves them if they are present and deletes all the rest... Sub DeleteNames() ' First seen posted by Bob Phillips Dim N As Name For Each N In ActiveWorkbook.Names If N.Name Like "*_FilterDatabase" Or _ N.Name Like "*Print_Area" Or _ N.Name Like "*Print_Titles" Or _ N.Name Like "*wvu.*" Or _ N.Name Like "*wrn.*" Or _ N.Name Like "*!Criteria" Then Else N.Delete End If Next N End Sub -- Rick (MVP - Excel) "AZSteve" wrote in message ... I have 5-15 sheets in a workbook and I want to delete all the names (25 or so) in each sheet in the workbook. I have not figured out a way to do this reliably yet with a macro. Then I need to do it again on the next workbook. Suggestions? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I delete all named ranges in a workbook or worksheet?
The list of names that Rick avoided deleting wasn't for names that he assigned.
These are names that excel uses (without your permission and usually without your knowledge). If you delete any of them, you may be breaking a feature built into excel. I'd use Rick's version. AZSteve wrote: I want to delete all names, so the other posts gave me that answer. However I will save yours for when I want to keep certain names. Thanks. "Rick Rothstein" wrote: There appear to be certain range names that you should not delete, so here is a macro that preserves them if they are present and deletes all the rest... Sub DeleteNames() ' First seen posted by Bob Phillips Dim N As Name For Each N In ActiveWorkbook.Names If N.Name Like "*_FilterDatabase" Or _ N.Name Like "*Print_Area" Or _ N.Name Like "*Print_Titles" Or _ N.Name Like "*wvu.*" Or _ N.Name Like "*wrn.*" Or _ N.Name Like "*!Criteria" Then Else N.Delete End If Next N End Sub -- Rick (MVP - Excel) "AZSteve" wrote in message ... I have 5-15 sheets in a workbook and I want to delete all the names (25 or so) in each sheet in the workbook. I have not figured out a way to do this reliably yet with a macro. Then I need to do it again on the next workbook. Suggestions? . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet with named ranges to new workbook and keep names in | Excel Programming | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Delete all named ranges in a workbook | Excel Programming | |||
macro to delete all named ranges in a workbook en masse? | Excel Discussion (Misc queries) | |||
named ranges at workbook and worksheet levels | Excel Programming |