CODEWITHSUNDEEP
excelvba
WoeIs
WoeIs

Reputation: 1083

Is there a way to automatically specify the range of a ComboBox without having to manually change it?

I have a column that is dynamic, so the length of the column can vary.

enter image description here

In this case, if I specify the ListFillRange to be A:A, it will add a lot of empty cells to the ComboBox. I want it to only add the cells that are not empty, so in this case A1, A2, and A3. If however, the length of the column changes to A1:A4, I want the ComboBox to automatically add that fourth cell, without manually having to change the Fill Range.

Upvotes: 0

Views: 66

Answers (1)

41686d6564
41686d6564

Reputation: 19661

You can use the Worksheet_Change event to change the ListFillRange whenever a cell in column A is changed. Assuming the sheet is Sheet1 and your ComboBox name is ComboBox1, you can do something like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    With Sheet1
        If Intersect(Target, .Range("A:A")) Is Nothing Then Exit Sub

        Dim lastRow As Long
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .ComboBox1.ListFillRange = "A1:A" & lastRow
    End With
End Sub

Note that this will keep any empty cells between the first row and lastRow. If that's not the behavior you want, then you can replace the line:

.ComboBox1.ListFillRange = "A1:A" & lastRow

..with:

'.ComboBox1.ListFillRange = ""    ' You can do this manually.
.ComboBox1.Clear
Dim i As Long, cell As Range
For i = 1 To lastRow
    Set cell = .Cells(i, 1)
    If Not IsEmpty(cell) Then .ComboBox1.AddItem (cell.Value)
Next

Upvotes: 1

Related Questions