Microsoft® Excel Macros for
Applying Range Names to Cells
A collection of macros for assigning, re-assigning and deleting range names in Excel worksheets.
Last updated on 2024-05-03.
Introduction
Consider a spreadsheet in which you've 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 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:
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.
I don't 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:
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:
And “Feb” is the range name applying to cells C2:C4:
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 aren't necessarily fixed throughout the lifetime of the macro.
Published on the web you'll find lots of attempts to code a macro that without user intervention will determine the exact area to be named. None of those attempts 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.