How to optimize slow performance when manipulating large datasets in Excel VBA?
I'm working through a tutorial and I'm currently working on a VBA script that processes a large dataset in Excel, roughly 100,000 rows, where I need to filter and copy specific values to a new sheet based on certain criteria....... However, I've noticed that the performance is quite slow, especially when using loops to iterate through each row. I've tried turning off screen updating and calculation with the following code: ```vba Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ``` But the process still takes several minutes to complete. Here's the main part of my code: ```vba Sub OptimizeDataProcessing() Dim wsSource As Worksheet Dim wsDest As Worksheet Dim lastRow As Long Dim i As Long Dim destRow As Long Set wsSource = ThisWorkbook.Sheets("Data") Set wsDest = ThisWorkbook.Sheets("FilteredData") lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row destRow = 1 For i = 1 To lastRow If wsSource.Cells(i, 1).Value > 1000 Then wsDest.Cells(destRow, 1).Value = wsSource.Cells(i, 1).Value destRow = destRow + 1 End If Next i Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub ``` Iām also considering using the `AutoFilter` method to reduce the number of iterations but I'm unsure how to implement it correctly for this scenario. If there are other best practices or methods in VBA for handling large datasets more efficiently, I would love to hear them. Any insights would be appreciated! Has anyone else encountered this? Any advice would be much appreciated. This issue appeared after updating to Vba 3.10. Is there a better approach?