A worksheet change event to handle an expanding named range


Well-known Member
Jun 30, 2005
Sometimes there is a method to my madness. Thanks to the help I got on the board this morning, I was able to solve a problem that was bugging me for a while. Whenever I got a new biweekly client, I had to add the name to my client named range. This required me to delete the range name, and then create it again (after I added the new clients name). With the help I got this morning (from Jaafar and Oaktree) in knowing the last row of a range, I was able to automate the whole process. Here is the code if anyone would like it for their own use. (You will need to change the range to suit of course). BTW, it is only fair to mention that my ability to write a Worksheet Change event came from this board as well!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim last_cell
With Range("name1")
last_cell = Right(.Address(True, False), Len(.Address(True, False)) - _
InStr(1, .Address(True, False), ":"))
last_cell = Val(Right(last_cell, InStr(1, last_cell, "$")))
End With
If Target.Cells.Count = 1 And Target.Column = 1 And Target.Row = (last_cell + 1) Then
ActiveWorkbook.Names.Add Name:="name1", RefersToR1C1:="=Sheet1!R1C1:R" _
& (last_cell + 1) & "C4"
End If
End Sub

Gene, "The Mortgage Man", Klein

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Von Pookie

MrExcel MVP
Feb 17, 2002
Or, you could just use a dynamic named range and forget about macros altogether.

For example, I have the following list:
ECR list.xls
3M01C01General informationJim
4M01C08General info - engine equip itemCarol
5M01E01Coloring parts listSteve
6M01E05Coloring informationAnn

It will always be 3 columns wide, but the number of rows is actually much longer than this--and at any time I may add/remove items.

Insted of creating the named range again and again, I just used a formula in place of the cell references. So instead of =Sheet1!$A$2:$C$6, I used:

This will automatically count the number of non-blank cells in column A (-1 because I don't want to include the headers in row 1) to find the "height" of the list and since the column width is static, I can just tell it 3 for the "width" of the list.

The only drawback I have with this, personally is that if you do it this way, you can't select the name from the dropdown available in the namebox or from goto. You have to manually enter the range name in the box or goto window to select it. Other than that, it works perfectly for me. I haven't had to worry about editing the range since :)


Well-known Member
Jun 30, 2005
I see. So your formula "changes" the size UP or DOWN (my way only does UP) automatically. Nice. I think I'll go with your way. Nice time I wont have any methods. I will just say what I am trying to do.

Gene, "The Mortgage Man", Klein

Joe Was

MrExcel MVP
Feb 19, 2002
Just a side note for a nother way, using in-cell-validation dropdown list. The code below will re-write the validation list if a value is added to the cell that is not on the list, so it will be part of that dropdown's list the next time it is used:

Private Sub Worksheet_Change(ByVal Target As Range)
'Run from the Sheet Module that the dropdown is on!

Dim myOldLst$, myNewLst$

If Target.Address = "$E$6" Then
myOldLst = Target.Validation.Formula1
myNewLst = myOldLst & "," & Target.Value

With Target.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Operator _
:=xlBetween, Formula1:=myNewLst

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ErrorTitle = "Warning!"

.ErrorMessage = _
"You may add any value!" & Chr(10) & "" & _
Chr(10) & "If you add a value not on the list it " & _
"will be added to the list the next time it is used!"
.ShowInput = False
.ShowError = True
End With
End If

End Sub

Watch MrExcel Video

Forum statistics

Latest member