Delete named ranges in VBA - by scope?

  • Thread starter Thread starter Revolvr
  • Start date Start date
R

Revolvr

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!
 
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!
Click to expand...

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
 
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!
Click to expand...

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
 
Back
Top