CODEWITHSUNDEEP
arraysexceloptimizationlistboxvba
user3627579
user3627579

Reputation:

Fill listbox.list from array

            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?

Upvotes: 1

Views: 3918

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

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

Upvotes: 1

Related Questions