CODEWITHSUNDEEP
excelvbacomboboxrange
MilkyTech
MilkyTech

Reputation: 1927

Populate ComboBox with dynamic .ListFillRange

I've had a lot of trouble trying to populate a combobox in a worksheet (not in a userform) with a dynamic range from another workbook. I have finally gotten it to populate by copying and pasting the range from the other workbook to this worksheet then using .ListFillRange, however, I cannot seem to figure out how to code this .ListFillRange to be dynamic. The second part of my question is - Is it possible to populate the combobox starting with the first line of the combobox? I don't need or want the blank user-input line at the top.

This is what I have:

ActiveSheet.Shapes("ComboBox1").Select
With Selection
    .ListFillRange = "ReprintOld!$U$2:$U$20"
End With

But I want something like this:

ActiveSheet.Shapes("ComboBox1").Select
With Selection
    .ListFillRange = "ReprintOld!$U$2:End(xlDown)"
End With

Upvotes: 0

Views: 2700

Answers (1)

Tim Williams
Tim Williams

Reputation: 166331

ActiveSheet.Shapes("ComboBox1").Select
With Selection
    .ListFillRange = "ReprintOld!$U$2:$U$" & sheets("ReprintOld").range("U2").End(xlDown).Row
End With

Upvotes: 1

Related Questions