Increase the list fill range with vba

[dpaton05]

How do I increase the list fill range so that when I add items to my list, a combo box that has a list of the items in it is updated automatically. The Listfillrange property of my combo box is YPNames!$A$2:$A48 but I want it to be A2 to the bottom of the list on YPNames.

 

[dpaton05]

I can't see where it is the same object twice as I was only running that procedure.

 

[dpaton05]

I think I have found out how you might be able to do it.

If you make the list of names a named range then put that name in the listfillrange, it appears to work. Only thing now is to when new names are added to the list, they will have to be added to the named range. Do you know how to do that?

 

[Michael M]

I would have thought a named range woul be the way to go....you could code the new name to be added to the namefrange...then the list will update witj it

 

[dpaton05]

How is that done?

 

[dpaton05]

I am trying to get it to select all the names entered in column A from A2. What is wrong with my code as it only selects the first name and the heading when there are 8? The heading is in A1.

Sub aIncreaseNamedRange()

    With ActiveWorkbook.Names("myRange")
        .Name = "myRange"
        .RefersToR1C1 = "=YPNames!R2C1:R" & Range("A1").End(xlUp).Row & "C1"
        .Comment = ""
    End With
    Application.Goto Reference:="myRange"
End Sub

 

[Michael M]

you can't combine R1C1 with normal range references
So, either R1C1 or range("A" ........

 

[dpaton05]

I even tried this code and it didn't work.

Sub AddNameList()

Dim R As Range
Set R = Range("myRange")
R.Name.RefersTo = YP.Range("A2:A" & Range("A:A").End(xlUp).Row)

End Sub

 

[dpaton05]

I think I want to steer away from the r1c1 references as I find them confusing.

 

[Michael M]

But as I mentioned, if you add a new name via D5 on the Tracker sheet, it updates the name in the list AND the NamedRange

 

[Michael M]

Make your named range dynamic for starters

Dynamic Named Range in Excel

A dynamic named range in Excel expands automatically when you add a value to the range.

www.excel-easy.com

The have the list fill refer to the Named Range rather than cell references