A worksheet change event to handle an expanding named range

[mortgageman]

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("name1").Delete
ActiveWorkbook.Names.Add Name:="name1", RefersToR1C1:="=Sheet1!R1C1:R" _
& (last_cell + 1) & "C4"
End If
End Sub


Gene, "The Mortgage Man", Klein

 

[Von Pookie]

Or, you could just use a dynamic named range and forget about macros altogether.

For example, I have the following list:

ECR list.xls
	A	B	C	D
1	UPG	UPGNAME	Manager
2	M01A01		John
3	M01C01	General information	Jim
4	M01C08	General info - engine equip item	Carol
5	M01E01	Coloring parts list	Steve
6	M01E05	Coloring information	Ann
Sheet1

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:
=OFFSET('Sheet1'!$A$2,0,0,COUNTA('Sheet1'!$A:$A)-1,3)

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

 

[mortgageman]

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]

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
.Delete
.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