VBA - Filling Combo Box with Dynamic Range

Question

I have a problem with my ComboBox1, I am trying to make some hidden filters to make viewing lots of data easier and i want to select what i filter out via a combo box, where the input selection is part of this processed data.

Here is some code that i tried to write for populating the ComboBox but it comes up with an error saying that it is the wrong use of Method.

Sub ComboBox1_DropButton_Click()
Dim i As Range

With Sheets("Pipe 16")
Set i = .Range("G5:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
End With
Me.ComboBox1.ListFillRange = "i"

End Sub

Any help appreciated.

EDIT

this cannot be answered by the answers shown in Dynamically set ListFillRange in Excel ComboBox using VBA

Possible duplicate of Dynamically set ListFillRange in Excel ComboBox using VBA — Dave
Have just tried what is suggested in here and it doesnt work @Dave — dyslexicgruffalo
You realize you're trying to give it the string i? Not whatever was set to the variable in your With statement? Try Me.ComboBox1.ListFillRange = "=i". — BruceWayne

Unknown Unknown · Accepted Answer · 2016-07-14T16:14:08

ListFillRange accept the range's address not the range itself.

Sub ComboBox1_DropButton_Click()

    Dim i As Range

    With Sheets("Pipe 16")
        Set i = .Range("G5:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
    End With
    Me.ComboBox1.ListFillRange = i.Address

End Sub

VBA - Filling Combo Box with Dynamic Range

2 Answers