DMW logo
contact@consultdmw.com

Microsoft® Excel Macros for
Applying Range Names to Cells

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

David Wallis, last updated 2022-10-26.


Introduction

Consider a spreadsheet in which you have assigned the range name nmData to a block of cells. Those cells contain values that you wish to total. You use this formula:

=SUM(nmData)

Excel formula using range 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 nmData 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:

Effect of extending Excel range 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.

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, 2016, 2019 and Excel for Microsoft® 365. 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 sRangeNameMacro01()

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:="nmData", RefersTo:=rng

Set rng = Nothing

Set ws = Nothing

Set wb = Nothing

End Sub

sRangeNameMacro01 applies the range name — nmData in this instance — to the cells in the workbook in which you execute the macro. It applies nmData 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 sRangeNameMacro02 is my attempt at using variables to make the code easier to follow:

Sub sRangeNameMacro02()

Dim rng As Range

Dim wb As Workbook, ws As Worksheet

Dim wsName$

Dim rngCells$, rngName$


wsName$ = "Sheet2"

rngCells$ = "A1:D4"

rngName$ = "nmData"

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. sRangeNameMacro03 does this to pop up a message to the user on completion:

Sub sRangeNameMacro03()

Dim rng As Range

Dim wb As Workbook, ws As Worksheet

Dim wsName$

Dim rngCells$, rngName$

Dim msg$


wsName$ = "Sheet2"

rngCells$ = "A1:D4"

rngName$ = "nmData"

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


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:

Sample Excel spreadsheet of monthly profits

sRangeNameMacro04 shows the bare skeleton of the code:

Sub sRangeNameMacro04()

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:

Range name 'Profit' applied to Excel worksheet

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

Range name 'Feb' applied to Excel spreadsheet


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. sRangeNameMacro05 is the skeleton code for achieving this. Important: see sRangeNameMacro06 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 sRangeNameMacro05()

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 sRangeNameMacro06, which is sRangeNameMacro05 with rudimentary error handling applied:

Sub sRangeNameMacro06()

On Error Goto errHandler

Dim rng As Range

Dim wb As Workbook, ws As Worksheet

Dim wsName$

Dim rngCells$, rngName$

Dim rsp$

Dim msg$, icon!, title$


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:

title$ = "Error Applying Range Names"

msg$ = _

"Please make note of following details." & _

vbNewLine & vbNewLine & _

Err.Number & ": " & Err.Description & _

vbNewLine & vbNewLine & _

"Please check your workbook before saving."

Resume procDone

End Sub


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

“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