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