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
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