PDA

View Full Version : Speed up VBA filtering



bowja
05-23-2017, 09:46 PM
Hello,

I have some VBA I have written which filters two tables (one above the other) and similar code that removes the filter. There are 1100 and 6000 records in the tables respectively. The filters are now taking 17 seconds to filter/unfilter and given the use case that is quite a nuisance. Would love it if any of the VBA gurus here could provide some tips or examples of how I could speed it up. NB the tables are updated when they are filtered.


Sub rowFilterByRoW()'
' rowFilterByRoW Macro
'
With Sheets("RoW Data")

Dim criteria As String
criteria = "*" & Range("rowDataRoWFilter").Value & "*"
Application.ScreenUpdating = False

.Range("RoWData[ROWID]").AutoFilter Field:=1, Criteria1:=criteria
.Range("RoWAddresses[ROWID]").AutoFilter Field:=1, Criteria1:=criteria
Application.ScreenUpdating = True
End With


Application.EnableEvents = True


End Sub
Sub rowClearFilter()
'
' rowClearFilter Macro
'


'
ActiveSheet.ListObjects("RoWData").Range.AutoFilter Field:=1
ActiveSheet.ListObjects("RoWAddresses").Range.AutoFilter Field:=1

End Sub

Bob Phillips
05-24-2017, 12:57 AM
I am afraid the code tells us nothing, we need to see the workbook to even attempt any improvements.

Jan Karel Pieterse
05-24-2017, 09:35 AM
Start the routine with

Applicaton.Calculation = xlCalculationManual
Application.ScreenUpdating = False
and end with

Applicaton.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True