delete sign pilcrow in combobox

[Lichress]

hi, tell, please, how to clean the sign pilcrow ( ¶ ) in combobox?

[Paul_Hossler]

Remove the Chr(10) in

Private Sub UserForm_Initialize()
    With ComboBox2
        .AddItem "text" & Chr(10) & "number"
    End With
End Sub

[Lichress]

I need to keep a cell format = text 1
number 1

[mdmackillop]

Remove the RowSource from the Combobox

Private Sub UserForm_Initialize()
    Dim txtlist As Range, cel, x As String
    With Sheets(2)
    Set txtlist = Range(.Cells(1, 1), .Cells(10, 1))
    End With
    
    With ComboBox1
        .AddItem "text" & vbTab & "number"
        For Each cel In txtlist
            x = Replace(cel.Value, Chr(10), vbTab)
            .AddItem x
        Next
    End With
End Sub

[snb]

@mdmac

Never use .additem or rowsource to populate a combobox/listbox; use .List instead.

See http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

[mdmackillop]

Alternative

Private Sub UserForm_Initialize()
    Dim txtlist As Range, cel, x As String
    Dim arr
    With Sheets(2)
         'arr needs an extra cell to hold the Combobox Header
        arr = Range(.Cells(1, 1), .Cells(10, 1)(2)).Value
    End With
    For a = UBound(arr) - 1 To 1 Step -1
        arr(a + 1, 1) = Replace(arr(a, 1), Chr(10), vbTab)
    Next
    arr(1, 1) = "Text" & vbTab & "Number"
    ComboBox1.List = arr
End Sub

[mdmackillop]

@snb
Not that I didn't believe you, but I checked speed of both methods above;
10 items over 5 runs
AddItem = 2436, List = 12976
1000 items over 5 runs
AddItem = 5235, List = 17408

[rlv]

FWIW, for me List was ~10 times faster than AddItem for 1000 items. Interestingly, for both methods it took longer to populate a listbox than a combo box.

[Lichress]

thank you very much