Fill listbox.list from array

Question

            x= range("A1:d100000").value
            For i = 1 To UBound(x, 1)

            Sk = x(i, 1) & "·" & x(i, 2) & "·" & x(i, 3) & "·" & x(i, 4)

            If InStr(x(i,4), "text") Then
              s = s & "~" & Sk
            Else
            End If
            Next i


            ListBox1.List = Split(Mid$(s, 2), "~")

thi code put match result to (ActiveX) listbox.list 1 column

How to put match result to listbox.list depending by SK number of columns, so

if x(i, 1) & "·" & x(i, 2) 
so put x(i,1) to listbox 1 column, x(i,2) to listbox 2 column

if x(i, 1) & "·" & x(i, 2) & "·" & x(i, 3)
so put x(i,1) to listbox 1 column, x(i,2) to listbox 2 column, x(i,3) to listbox 3 column

this code do this well but takes three times longer

   q = Split(Mid$(s, 2), "~")

    With ListBox1
        .Clear
        For Li = LBound(q) To UBound(q)
            q1 = Split(q(Li), "·")
            .ColumnCount = UBound(q1) + 1
            .ColumnWidths = Replace(Space(UBound(q1)), " ", "200;") & 200
            .AddItem q1(0), Li
            For Li2 = 1 To UBound(q1)
                .List(Li, Li2) = q1(Li2)
            Next Li2
        Next Li
'        Stop
    End With

It is anyway to speed up this code?

You cannot use ActiveSheet.Shapes("ListBox1").OLEFormat.Object.ListFillRange = "A1:D100000" directly? — user3514930
– user3514930, Commented May 12, 2014 at 11:13

Dick Kusleika · Accepted Answer · 2014-05-12 13:54:52Z

1

When you read in the .Value property from a Range, you get a 1-based array. An ActiveX Listbox's List property wants a 0-based array. The .AddItem property is always slow and you should use it when you only have a few items. For anything more than a few items, convert your data into a 0-based array and use the List property.

Sub FillLb()

    Dim vaRange As Variant
    Dim aList() As Variant
    Dim i As Long, j As Long

    vaRange = Sheet1.Range("A1:D100000")
    ReDim aList(0 To UBound(vaRange, 1) - 1, 0 To UBound(vaRange, 2) - 1)
    Sheet1.ListBox1.ColumnCount = UBound(vaRange, 2)

    For i = LBound(vaRange, 1) To UBound(vaRange, 1)
        For j = LBound(vaRange, 2) To UBound(vaRange, 2)
            aList(i - 1, j - 1) = vaRange(i, j)
        Next j
    Next i

    Sheet1.ListBox1.List = aList

End Sub

answered May 12, 2014 at 13:54

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fill listbox.list from array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related