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.

 

[My Aswer Is This]

What is YPNames

Is this a Named Range or a Sheet Name
Is this Listbox on a sheet or a Userform.

 

[My Aswer Is This]

This script will do what you want but to have this run automatically when you add a value to the range. Would take more work.

Private Sub CommandButton1_Click()
'Modified  5/11/2021  10:27:23 PM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ListBox1.List = Range("A2:A" & Lastrow).Value
End Sub

 

[dpaton05]

Thanks for the reply, it is using a combo box and YPNames is the sheet name

 

[dpaton05]

I changed to the name of the combo box but i get the error of variable not defined, with cboYP highlighted. This is the name of my combo box

Sub AddNameList()
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    cboYP.List = Range("A2:A" & Lastrow).Value
End Sub

 

[dpaton05]

With this code I get the error permission denied.

Sub AddNameList()
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Tracker.cboYP.List = Range("A2:A" & Lastrow).Value
End Sub

 

[Michael M]

Have you got Option Explicit turned on ??

Sub AddNameList()
   cboYP.List = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
End Sub

 

[dpaton05]

yes

 

[dpaton05]

I think I need to update the list fill range property to be 1 greater in column A using vba but I am not sure how to do that.

 

[Michael M]

variable not defined, with cboYP highlighted

....means you didn't declare cboYP