PDA

View Full Version : Remove gaps in a combobox list



Helpmepls
03-02-2013, 03:02 AM
Hi, I have a combobox on a userform which has spaces in the combobox list. Can anyone tell me if there is a way to remove the gaps? The code I have is:



Me.ComboBox2.List = Worksheets("QuestionSet2").Range("a1:a25").Value


Thanks

Paul_Hossler
03-03-2013, 09:52 AM
If they're blank, I'd just not add them in the first place


Option Explicit
Sub FillAndShow()
Dim r As Range
Dim i As Long
Set r = Worksheets("QuestionSet2").Range("a1:a25")
Load UserForm1
With r
For i = 1 To 25
If Len(.Cells(i, 1).Value) > 0 Then
Call UserForm1.ComboBox2.AddItem(.Cells(i, 1).Value)
End If
Next i
End With

UserForm1.ComboBox2.ListIndex = 0
UserForm1.Show
End Sub


Paul

snb
03-03-2013, 12:59 PM
ComboBox2.List = filter([transpose(if(QuestionSet2!a1:a25="","~",QuestionSet2!a1:a25))],"~",false)

Helpmepls
03-31-2013, 03:11 PM
Thanks Paul and snb.