Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete named ranges in VBA - by scope?
Is there a way to delete all occurrences of a named range that is
scoped to a worksheet level only? I have a workbook with workbook level named ranges. People sometimes copy a sheet resulting in another occurrence of the same named range, but scoped to the worksheet level. This screws up a bunch of charts. I need a way to delete these if it happens. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete named ranges in VBA - by scope?
On Jul 22, 8:53*pm, Revolvr wrote:
Is there a way to delete all occurrences of a named range that is scoped to a worksheet level only? I have a workbook with workbook level named ranges. People sometimes copy a sheet resulting in another occurrence of the same named range, but scoped to the worksheet level. This screws up a bunch of charts. I need a way to delete these if it happens. Thanks! Phillip London UK Try this Sub RemoveSheetLevelNames() For r = 1 To Names.Count If InStr(1, Names(r).Name, "!") Then MsgBox Names(r).Name & " is a sheet level name" Names(r).Delete End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete named ranges in VBA - by scope?
Revolvr has brought this to us :
Is there a way to delete all occurrences of a named range that is scoped to a worksheet level only? I have a workbook with workbook level named ranges. People sometimes copy a sheet resulting in another occurrence of the same named range, but scoped to the worksheet level. This screws up a bunch of charts. I need a way to delete these if it happens. Thanks! I strongly urge you to NOT delete names with local scope! I think it's best if you rethink your names design. The problem when copying sheets lies with the workbook scope names, NOT the sheet level names. Sheet level (local scope) names are unique to each sheet and will travel with the sheet without name conflicts. It's considered good design and best practice to use local defined names by default. Workbook level names also travel with the sheet and raise name conflicts when copied. (Applies to copying in the same workbook OR another workbook using the same global scope name) It's considered good design and best practice to use workbook level (global scope) names ONLY WHEN ABSOLUTELY NECESSARY. Also, NEVER define a global name to exactly the same name as one with local scope. Naming Examples: Sheet1!TransactionDate = Sheet1!$A1 (local to Sheet1 only) Sheet2!TransactionDate = Sheet2!$A1 (local to Sheet2 only) TransactionDate = Sheet3!$A$1 (global scope available to any sheet) This name copies with sheets + raises a name conflict notification. Bad idea since this is exactly the same as names used on other sheets, and so forces need to explicitly ref the name on the sheets using it locally so that formulas calc correctly. Usage Examples: Formula on Sheet1 refs local name: =Sheet1!TransactionDate+30 (refs Sheet1!$A+Row() where used) Formula on Sheet1 refs global name: =TransactionDate+30 (refs Sheet3!$A$1) Formula on Sheet2 refs local name on Sheet1: =Sheet1!TransactionDate+30 (refs Sheet1!$A+Row() where used on sheet2) Formula on Sheet2 refs local name on Sheet2: =Sheet2!TransactionDate+30 (refs Sheet2!$A+Row() where used) By simply using a naming convention for global names that differs from the naming convention for local names obviates a tonne of troubles. Transaction_Date = Sheet3!$A$1 (global scope available to any sheet) To use: Formula on Sheet1 refs local name: [use name directly] =TransactionDate+30 (refs Sheet1!$A+Row() where used) Formula on Sheet1 refs global name: [use name directly] =Transaction_Date+30 (refs Sheet3!$A$1) Formula on Sheet2 refs local name on Sheet1: [no change of use] =Sheet1!TransactionDate+30 (refs Sheet1!$A+Row() where used) Formula on Sheet2 refs local name on Sheet2: [use name directly] =TransactionDate+30 (refs Sheet2!$A+Row() where used) HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete unnecessary Named Ranges | Excel Discussion (Misc queries) | |||
Deleting all named ranges that have a workbook scope | Excel Discussion (Misc queries) | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Can't delete named ranges? | Excel Programming | |||
Delete LOCAL named ranges | Excel Programming |