VBA Express : Excel - Redefine All Range Names

Excel

Redefine All Range Names

Ease of Use

Easy

Version tested with

2000, 2003

Submitted by:

geekgirlau

Description:

Deletes all existing range names in a workbook, then recreates them from a list in the active sheet.

Discussion:

When you are working with a large number of ranges, redefining them can be a painful process. For example, if you had 20 ranges that you want to extend from Rows 5-750 to rows 5-1000, it would be much quicker to do a search and replace on the row number rather than defining each range individually. Another example is that you want to create 12 new ranges for a new financial year on a new sheet. The quickest option would be to copy the ranges for the current financial year, then just search and replace the year. This code assumes that you have pasted a list of your existing range names on a new sheet, then made some changes. You can change the dimensions of existing ranges (either by typing them or using search and replace), delete or add ranges.

Code:

instructions for use

			Option Explicit 
 
Sub RedefineRange() 
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     ' Purpose:      Remove all existing range names in the workbook, then
     '               recreate them from a list
     '
     ' Assumption:   The user has previously extracted a list of all range
     '               names using Insert | Name | Paste | Paste List in cell A1
     '               of a blank sheet, then modified, added or deleted names
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    Dim nm As Name ' each name in workbook
    Dim rng As Range ' list of range names to recreate
     
     
    On Error GoTo ErrHandler 
     
    If Range("A1").Formula = "" Then 
        MsgBox "No range names detected", vbInformation, "No Data" 
         
    Else 
         ' user to confirm before proceeding
        If vbYes = MsgBox("This will remove all existing range names and " & vbCrLf & _ 
        "only recreate the names listed here." & vbCrLf & vbCrLf & _ 
        "Do you want to continue?", _ 
        vbYesNo + vbQuestion + vbDefaultButton2, _ 
        "Redefine Range Names") Then 
             
             ' remove all existing range names
            For Each nm In ActiveWorkbook.Names 
                Application.StatusBar = "Removing range name " & nm.Name 
                nm.Delete 
            Next nm 
             
            Application.DisplayAlerts = False 
             
            For Each rng In Range(Range("A1"), Range("A1").End(xlDown)) 
                Application.StatusBar = "Adding range " & rng.Formula 
                 
                ActiveWorkbook.Names.Add rng.Formula, rng.Offset(0, 1).Formula, True 
            Next rng 
             
            Range("A1").Select 
            Selection.ListNames 
        End If 
    End If 
     
ExitHere: 
    Application.StatusBar = False 
    Application.DisplayAlerts = True 
    Exit Sub 
     
ErrHandler: 
    MsgBox Err.Number & ": " & Err.Description 
    Resume ExitHere 
End Sub

How to use:

Copy the code above
Press [Alt-F11] to view the VBE window
Select the VBA project for a specific workbook, or PERSONAL.XLS if you want the macro available all the time
Select Insert | Module
Paste the code
Press [Alt-F4] to close the VBE window

Test the code:

Open the attached workbook "Redefine Ranges.xls"
Have a look at the existing range names - you'll notice they don't extend to the last row of data.
Insert a new sheet (Insert | Worksheet).
Move to cell A1.
Insert a list of your range names (Insert | Name | Paste | Paste List).
Change the dimensions of the ranges to cover all the data. Select Edit | Replace, and replace "$200" with "$500". Don't worry if the cell in column B displays an error at this time.
To delete a range, delete the row listing the relevant range name. In the sample workbook, delete the row containing the range "UnnecessaryRange".
To add a range, copy an existing row and paste it at the bottom of the list. In the sample workbook, copy the ranges "Purch_2004.07" to "Purch_2005.06" and paste at the bottom of the list. Select the cells in column B for your new ranges, and replace "04.05" with "05.06". Select the range names in column A for your new ranges and replace "2005" with "2006", and "2004" with "2005".
Select Tools | Macro | Macros.
Double-click on "RedefineRange".
Answer "Yes" to proceed.
An updated list of your range names will be pasted into the sheet. Check that your modified and added range names are listed correctly, and that the deleted range names no longer appear.

Sample File:

Redefine Ranges.zip 92.43KB

Approved by mdmackillop

This entry has been viewed 230 times.