How to Load ListView the Fastest

[JarekM]

Hi, can anyone help me figure out (if there is a way) how to write this code in another way? There is a lot of data and it takes a while for it to load, so is there a way to make this code run faster, or does ListView just take so long to load?

 'load list view
lastLine = Sheet1.Cells(Sheet1.Cells.Rows.Count, "a").End(xlUp).row
    ListView1.ListItems.Clear
    For X = 2 To lastLine
        Set li = ListView1.ListItems.Add(Text:=Sheet1.Cells(X, "a").Value)
        li.ListSubItems.Add Text:=Sheet1.Cells(X, "c").Value
        li.ListSubItems.Add Text:=Sheet1.Cells(X, "d").Value
        li.ListSubItems.Add Text:=Sheet1.Cells(X, "q").Value
        li.ListSubItems.Add Text:=Sheet1.Cells(X, "r").Value
        li.ListSubItems.Add Text:=Sheet1.Cells(X, "s").Value
        li.ListSubItems.Add Text:=Sheet1.Cells(X, "u").Value
        li.ListSubItems.Add Text:=Sheet1.Cells(X, "l").Value
        li.ListSubItems.Add Text:=Sheet1.Cells(X, "m").Value
    Next

Thank you for any help.

 

[Domenic]

Reading values from the worksheet is inefficient. Instead, first transfer the worksheet values to an array, and then loop through the array to fill your listview...

    With Sheet1
   
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
       
        Dim data As Variant
        data = .Range("A1:U" & lastRow).Value
       
    End With
   
    Dim colIndices As Variant
    colIndices = Array(3, 4, 17, 18, 19, 21, 12, 13)

    Dim li As ListItem
    Dim rowIndex As Long
    Dim columnIndex As Long
    With ListView1
        .ListItems.Clear
        For rowIndex = 2 To lastRow
            Set li = .ListItems.Add(Text:=data(rowIndex, 1))
            For columnIndex = LBound(colIndices) To UBound(colIndices)
                li.ListSubItems.Add Text:=data(rowIndex, colIndices(columnIndex))
            Next columnIndex
        Next rowIndex
    End With

Hope this helps!

 

[JarekM]

Reading values from the worksheet is inefficient. Instead, first transfer the worksheet values to an array, and then loop through the array to fill your listview...

    With Sheet1
  
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
      
        Dim data As Variant
        data = .Range("A1:U" & lastRow).Value
      
    End With
  
    Dim colIndices As Variant
    colIndices = Array(3, 4, 17, 18, 19, 21, 12, 13)

    Dim li As ListItem
    Dim rowIndex As Long
    Dim columnIndex As Long
    With ListView1
        .ListItems.Clear
        For rowIndex = 2 To lastRow
            Set li = .ListItems.Add(Text:=data(rowIndex, 1))
            For columnIndex = LBound(colIndices) To UBound(colIndices)
                li.ListSubItems.Add Text:=data(rowIndex, colIndices(columnIndex))
            Next columnIndex
        Next rowIndex
    End With

Hope this helps!

Thank you for the reply. You resolved the issue and the program is working 100% faster. Although I still have one more question, how can I update this code if I want to add search to the ListView?

 

[Domenic]

Thank you for the reply. You resolved the issue and the program is working 100% faster.

That's great, glad I could help, and thanks for the feedback.

Although I still have one more question, how can I update this code if I want to add search to the ListView?

The code will depend on how you want the search to take place. For example...

1) Do you want to search for a listitem's text, subitems, or tag property?

2) Do you want to search for an exact match or partial match? If the latter, should the match occur at the beginning of the text string? Or can it occur anywhere within the text string?

However, since this is a new question, please start a new thread and post your question there.

Cheers!