VBA: How to dynamically filter a list based on multiple criteria without causing performance issues?
I'm having trouble with I'm attempting to set up Quick question that's been bugging me - I'm working on a VBA solution in Excel 2016 where I need to filter a large dataset based on multiple user-selected criteria. The dataset is around 10,000 rows, and I'm worried about performance as I add more filters. I've already set up a user form that takes the criteria, but when I apply the filters using the `AutoFilter` method, it takes a long time to process, especially when multiple criteria are involved. Here's the code snippet I currently have: ```vba Sub FilterData() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") Application.ScreenUpdating = False ws.AutoFilterMode = False Dim criteria1 As String Dim criteria2 As String criteria1 = Sheets("UserForm").Range("A1").Value ' User-selected criterion 1 criteria2 = Sheets("UserForm").Range("B1").Value ' User-selected criterion 2 ws.Range("A1:E10000").AutoFilter Field:=1, Criteria1:=criteria1 ws.Range("A1:E10000").AutoFilter Field:=2, Criteria1:=criteria2 Application.ScreenUpdating = True End Sub ``` When I run this code, it works but the performance is noticeably slow, especially with more than two criteria. I've tried using `Application.Calculation = xlCalculationManual` and wrapping my code in `Application.EnableEvents = False` and `True`, but it seems to make little difference. Is there a more efficient way to handle dynamic filtering in VBA? Are there best practices I should apply to optimize performance when dealing with larger datasets? Any suggestions or alternative approaches would be greatly appreciated! How would you solve this? My development environment is Ubuntu 22.04. Thanks for any help you can provide! My team is using Vba for this desktop app. Hoping someone can shed some light on this.