dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Macros for Applying Range Names to Cells

A growing collection of macros for assigning, re-assigning and deleting range names in Excel worksheets.

David Wallis, last updated 2018-08-10.


Introduction

Consider a spreadsheet in which you have assigned the range name MyData to a block of cells. Those cells contain values that you wish to total, so you use the formula =SUM(MyData).

Excel rnage name

Excel will be as helpful as it possiby can be in following any changes your users make that affect the named range and therefore the formula. For instance, if a user inserts a row or a column through the block, then Excel will adjust MyData to include them.

What Excel will not respond to is a user inputting into the row below, or the column to the right, of the block. Consequently your formula will not pick up on the new input:

Excel rnage name

This sets the challenge: to devise a macro that will re-assign a range name thus saving the user the chore of attempting to do that manually.


Using DMW’s Macros

To use any of the code shown on this page, select it and then copy and paste it into a module in your workbook. Best to test any macro thoroughly before relying on it for your own use. If you find anything wrong, please let me know so that I can put things right.

To keep the code listings as brief as possible, I’ve omitted any error-handling routines. You’ll find an example of one such at the end of this of this piece.

The macros on this page were developed to work in Excel versions 2010, 2013 and 2016. Please let me know if you have problems with them. DMW Consultancy Ltd does not accept liability for any loss or damage to data to which you subject any of these macros.


Range Naming Macros

When you know precisely to which block of cells in your active spreadsheet you want to assign a range name, this macro will do the job:

Sub Macro01()

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim rng As Range

Dim wb As Workbook, ws As Worksheet

Set wb = ActiveWorkbook

Set ws = wb.Worksheets("Sheet2")

Set rng = ws.Range("A1:D4")

ws.Names.Add Name:="MyData", RefersTo:=rng

Set rng = Nothing

Set ws = Nothing

Set wb = Nothing

End Sub

Macro01 applies the range name — MyData in this instance — to the cells in the workbook in which you execute the macro. It applies MyData to the block of cells A1:D4 in the worksheet Sheet2.

You find you have to read the macro carefully to discover the range’s name and the cells to which it’s being assigned. So Macro02 is my attempt at using variables to make the code easier to follow:

Sub Macro02()

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim rng As Range

Dim wb As Workbook, ws As Worksheet

Dim wsName$

Dim rngCells$, rngName$

wsName$ = "Sheet2"

rngCells$ = "A1:D4"

rngName$ = "MyData"

Set wb = ActiveWorkbook

Set ws = wb.Worksheets(wsName$)

Set rng = ws.Range(rngCells$)

ws.Names.Add Name:=rngName$, RefersTo:=rng

Set rng = Nothing

Set ws = Nothing

Set wb = Nothing

End Sub


Reusable Variables

Any of the variables you declare with Dim statements can be used more than once in your macro. Macro03 does this to pop up a message to the user on completion:

Sub Macro03()

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim rng As Range

Dim wb As Workbook, ws As Worksheet

Dim wsName$

Dim rngCells$, rngName$

Dim msg$

wsName$ = "Sheet2"

rngCells$ = "A1:D4"

rngName$ = "MyData"

Set wb = ActiveWorkbook

Set ws = wb.Worksheets(wsName$)

Set rng = ws.Range(rngCells$)

ws.Names.Add Name:=rngName$, RefersTo:=rng

Set rng = Nothing

Set ws = Nothing

Set wb = Nothing

msg$ = _

"Range name " & rngName$ & _

" applied to cells " & rngCells$ & _

" in " & wsName$

MsgBox msg$

End Sub


Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


Applying Multiple Range Names

You can make row and column headings the source of range names — for example, those in Row 1 and in Columns A to F in this spreadsheet:

Excel range names

Macro04 shows the bare skeleton of the code:

Sub Macro04()

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim rng As Range

Dim wb As Workbook, ws As Worksheet

Set wb = ActiveWorkbook

Set ws = wb.Worksheets("Sheet2")

Set rng = ws.Range("A1:F4")

rng.CreateNames Top:=True, Left:=True

End Sub

The macro projecs the headings in Row 1 down their respective columns, and the headings in Column A across their respective rows. To the effect that, for example, “Profit” is the range name applying to cells B4:F4:

Excel range names

And “Feb” is the range name applying to cells C2:C4:

Excel range names


Responsive Macros

Each of the macros above apply range names to blocks of cells the details of which are embedded in the macros. So, we now need to address the issue identified at the top of this page, that of ranges that are not necessarily fixed throughout the lifetime of the macro.

You’ll find lots of attempts, published on the web, to code a macro that, without user intervention, will determine the exact area to be named. None of those at which I’ve looked would work across the scores of spreadsheets I’ve met that needed such functionality.

I’ve failed to find the universal intelligent macro. That's despite some heavy-duty coding applied to specific spreadsheets. On occasion my hopes have run high only to have users find ingenious ways of running my macros onto the rocks.

So, what I sometimes do is pass responsibility to the user for identifying the block of cells to be named. Macro05 is the skeleton code for achieving this. Important: see Macro06 for code that includes error handling.

The VBA InputBox pauses the macro to gather user input; the rsp$ variable gathers the user’s input; and the rngCells$ variable composes the block of cells the user has identified:

Sub Macro05()

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim rng As Range

Dim wb As Workbook, ws As Worksheet

Dim wsName$

Dim rngCells$, rngName$

Dim msg$, icon!, title$, rsp$

wsName$ = "Sheet2"

title$ = "Range Naming of Data Cells"

msg$ = _

"Address of cell at top " & _

"left-hand corner of data?"

rsp$ = InputBox(msg$, title$)

If rsp$ = "" Then

' Do nothing

msg$ = "Range naming stopped short"

icon! = vbOKOnly + vbExclamation

Else

rngCells$ = rsp$

msg$ = _

"Address of cell at bottom " & _

"right-hand corner of data?"

rsp$ = InputBox(msg$, title$)

If rsp$ = "" Then

' Do nothing

msg$ = "Range naming stopped short"

icon! = vbOKOnly + vbExclamation

Else

rngCells$ = rngCells$ & ":" & rsp$

Set wb = ActiveWorkbook

Set ws = wb.Worksheets(wsName$)

Set rng = ws.Range(rngCells$)

rng.CreateNames Top:=True, Left:=True

icon! = vbOKOnly + vbInformation

msg$ = _

"Range name " & rngName$ & _

" applied to cells " & rngCells$ & _

" in " & wsName$

End If

End If

MsgBox msg$, icon!, title$

End Sub


Error Handling

Providing error handling for any macro, I regard as essential. Error Trapping and Handling has a full explanation of what start to make here in this Macro06, which is Macro05 with rudimentary error handling applied:

Sub Macro06()

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error Goto errHandler

Dim rng As Range

Dim wb As Workbook, ws As Worksheet

Dim wsName$

Dim rngCells$, rngName$

Dim msg$, icon!, title$, rsp$

wsName$ = "Sheet2"

title$ = "Range Naming of Data Cells"

msg$ = _

"Address of cell at top " & _

"left-hand corner of data?"

rsp$ = InputBox(msg$, title$)

If rsp$ = "" Then

' Do nothing

msg$ = "Range naming stopped short"

icon! = vbOKOnly + vbExclamation

Else

rngCells$ = rsp$

msg$ = _

"Address of cell at bottom " & _

"right-hand corner of data?"

rsp$ = InputBox(msg$, title$)

If rsp$ = "" Then

' Do nothing

msg$ = "Range naming stopped short"

icon! = vbOKOnly + vbExclamation

Else

rngCells$ = rngCells$ & ":" & rsp$

Set wb = ActiveWorkbook

Set ws = wb.Worksheets(wsName$)

Set rng = ws.Range(rngCells$)

rng.CreateNames Top:=True, Left:=True

icon! = vbOKOnly + vbInformation

msg$ = _

"Range name " & rngName$ & _

" applied to cells " & rngCells$ & _

" in " & wsName$

End If

End If

procDone:

MsgBox msg$, icon!, title$

Exit Sub

errHandler:

msg$ = Err.Number & ": " & Err.Descripion

Resume procDone

End Sub

“What’s in a name, what’s in my sleepy eyes half the time?
If you would have asked how I was, I’d have said I’m just fine.”

Late Night Alumni What’s In A Name