Excel VBA: How to optimize performance when searching for multiple criteria in large datasets?
I'm stuck on something that should probably be simple... I'm dealing with a large dataset in Excel (over 100,000 rows), and I'm trying to search for multiple criteria using VBA. The current implementation is slow and often takes several minutes to run. Here's the code I've been using: ```vba Sub SearchMultipleCriteria() Dim ws As Worksheet Dim lastRow As Long Dim searchValue1 As String Dim searchValue2 As String Dim foundCell As Range Dim results As Collection Dim result As Variant Set ws = ThisWorkbook.Sheets("Data") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row searchValue1 = "Criteria1" searchValue2 = "Criteria2" Set results = New Collection For i = 1 To lastRow If ws.Cells(i, 1).Value = searchValue1 Or ws.Cells(i, 2).Value = searchValue2 Then results.Add ws.Cells(i, 1).Value End If Next i ' Output the results For Each result In results Debug.Print result Next result End Sub ``` While this works, the performance is quite poor. I receive a message stating "Excel is not responding" when running the macro, and it often crashes if I try to run it multiple times. I've tried turning off screen updating with `Application.ScreenUpdating = False`, but it didn't seem to help much. Additionally, I also considered using `Find` or `AutoFilter`, but I'm unsure how to implement that effectively in this scenario. Could anyone suggest a more efficient approach to search for multiple criteria in such a large dataset? Any advice on leveraging Excel's built-in functions or optimizing my current VBA code would be greatly appreciated! For context: I'm using Vba on Linux. I'd really appreciate any guidance on this. This is part of a larger API I'm building. How would you solve this?