User form that searches a spreadsheet I have this userform that looks up into a sheet. The problem is that when there are a lot of rows to look up it takes too long to execute (for example 100 thousand rows). Is it possible to reduce the time?

Here is the userform's textbox code where I put what I am looking for, and the data is shown on a listbox ("LSTART")

Private Sub TXTBUSCAART_Change()
    Dim rowCount As Long, itemCount As Long, counter As Long, n As Long
    Dim dataSheet As Worksheet
    Dim dataIn, dataOut()

    LSTART.Clear
    LSTART.ColumnCount = 9

    Set dataSheet = Sheets("CONCAT")
    With dataSheet

        rowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
        itemCount = Application.WorksheetFunction.CountIf(.Range("A2:A" &amp; rowCount), "*" &amp; TXTBUSCAART.Text &amp; "*")

        If itemCount &gt; 0 Then
            ReDim dataOut(1 To itemCount, 1 To 9)
            dataIn = .Range("A2:I" &amp; rowCount).Value
            counter = 1

            For n = 1 To UBound(dataIn)
                M = InStr(1, dataIn(n, 1), UCase(TXTBUSCAART.Text))
                If M &gt; 0 Then
                    dataOut(counter, 1) = dataIn(n, 1)
                    dataOut(counter, 2) = dataIn(n, 3)
                    dataOut(counter, 3) = dataIn(n, 2)
                    dataOut(counter, 4) = dataIn(n, 4)
                    dataOut(counter, 5) = dataIn(n, 6)
                    dataOut(counter, 6) = dataIn(n, 5)
                    dataOut(counter, 7) = dataIn(n, 8)
                    dataOut(counter, 8) = dataIn(n, 9)
                    dataOut(counter, 9) = dataIn(n, 7)
                    counter = counter + 1
                End If
            Next

        LSTART.List = dataOut

        End If
    End With

End Sub
Worksheet functions like CountIf are pretty well optimized, so that's a good way to find out if TXTBUSCAART.Text is in column A of your dataSheet. but so is Find. 

By using Find, you could eliminate the initial step (CountIf) to determine if your search string is in the destination and you eliminate the InStr call to recheck if it's in each individual row of your data set. Unfortunately, you really need to know how many times your .Text appears to make the most efficient use of ReDim dataOut, so we'll leave that in there.

This is what I came up with for your loop using Find:

Private Sub LStart_Change()

  Dim rowCount As Long, itemCount As Long, counter As Long
  Dim dataSheet As Worksheet
  Dim dataOut() As Variant

  LStart.Clear
  LStart.ColumnCount = 9

  Set dataSheet = Sheets("sheet1")
  With dataSheet
    rowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
    Dim searchString As String
    searchString = "abcde"
    itemCount = Application.WorksheetFunction.CountIf(.Range("A2:A" &amp; rowCount), "*" &amp; searchString &amp; "*")
    If itemCount &gt; 0 Then
      Dim searchRange As Range
      Set searchRange = .Range(.Cells(2, 1), .Cells(rowCount, 1))
      ReDim dataOut(1 To itemCount, 1 To 9)
      Dim foundCell As Range
      Set foundCell = searchRange.Find(What:=searchString, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
      counter = 1
      Dim firstCell As Range
      Do Until foundCell Is Nothing
        If firstCell Is Nothing Then
          Set firstCell = foundCell
        End If
        With foundCell
          dataOut(counter, 1) = .Cells(1, 1)
          dataOut(counter, 2) = .Cells(1, 3)
          dataOut(counter, 3) = .Cells(1, 2)
          dataOut(counter, 4) = .Cells(1, 4)
          dataOut(counter, 5) = .Cells(1, 6)
          dataOut(counter, 6) = .Cells(1, 5)
          dataOut(counter, 7) = .Cells(1, 8)
          dataOut(counter, 8) = .Cells(1, 9)
          dataOut(counter, 9) = .Cells(1, 7)
        End With
        Set foundCell = searchRange.FindNext(foundCell)
        If foundCell.Address = firstCell.Address Then
          Set foundCell = Nothing
        Else
          counter = counter + 1
        End If
      Loop
      LStart.List = dataOut
    End If
  End With

End Sub


It compiles cleanly, but you may have to fiddle with the .Find() parameters a little bit since I don't have your exact data set to work from.

Using .Find() takes you directly to each row that has your TXTBUSCAART.Text in it so you may save 100s of If InStr() comparisons in a row looking at rows that don't contain your required text. I've set MatchCase:=False to eliminate the UCase() call on each comparison since it didn't seem to matter (you don't UCase()the text in your initial CountIf(), so I didn't think you really cared - adjust as necessary).

I set the variable searchString to TXTBUSCAART.Text one time because there's no need to reference the form every time through the loop. This may well save you milliseconds in total execution time! :)

When using .Find() it will continue to loop through the search range forever, so you have to store off the first cell it finds then compare each newly found cell to that one to stop the loop when you've returned to the beginning.

One note on .Find(): it will use any parameters set in the Find dialog box that you haven't explicitly overwritten, so make sure you're including all the parameters you need. Also, any parameters you set in code will appear the next time you open the Find dialog box, so if you have a favorite group of settings, you may want to store them off before fiddling with them in your code, then reset them when you're done.