Table criteria filter

Question

My tables retrieve entries from the database through a dropdown list so they can have 1 to 500 entries, therefore auto-filter to remove blanks is inevitable.

I have tables across many sheets, however , hey don't start from the same row or column. In order to filter out blanks, I had to record a macro and assign it to a button but it takes about 30 seconds to process.

Is there a way to cut the processing time? Perhaps we don't have to choose each table/sheet individually but as a whole?

sub filteroutblanks()
 Sheets("Talent OutFlow").Select
 ActiveSheet.ListObjects("TalentOutflow").Range.AutoFilter Field:=1, _
 Criteria1:="<>"
 Sheets("One-Pager Profile").Select
 ActiveSheet.ListObjects("Table18").Range.AutoFilter Field:=1, Criteria1:= _
 "<>"
 Sheets("Internal Promotions").Select
 ActiveSheet.ListObjects("InternalPromotions").Range.AutoFilter Field:=1, _
 Criteria1:="<>"
 Sheets("External Hires").Select
 ActiveSheet.ListObjects("ExternalHires").Range.AutoFilter Field:=1, _
 Criteria1:="<>"
 Sheets("Talent Inflow").Select
 ActiveSheet.ListObjects("TalentInflow").Range.AutoFilter Field:=1, Criteria1 _
 :="<>"
 Sheets("Exceptions-Overheads").Select
 ActiveSheet.ListObjects("StatusExceptions").Range.AutoFilter Field:=1, _
 Criteria1:="<>"
 Sheets("Talent Calibrations").Select
 ActiveSheet.ListObjects("Calibrations").Range.AutoFilter Field:=1, Criteria1 _
 :="<>"
 Sheets("Current CDN-U").Select
 ActiveSheet.ListObjects("CurrentCDNorU").Range.AutoFilter Field:=1, _
 Criteria1:="<>"
 Sheets("Exits").Select
 ActiveSheet.ListObjects("LeaversTable").Range.AutoFilter Field:=1, Criteria1 _
 :="<>"
 Sheets("Demotions").Select
 ActiveSheet.ListObjects("DemotionsORexits").Range.AutoFilter Field:=1, _
 Criteria1:="<>"
 Sheets("Current Vacancies").Select
 ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=1, Criteria1:= _
 "<>"
 Sheets("Language").Select
 ActiveSheet.ListObjects("Languages").Range.AutoFilter Field:=1, Criteria1:= _
 "<>"
 Sheets("Mobility").Select
 ActiveSheet.ListObjects("Mobility").Range.AutoFilter Field:=1, Criteria1:= _
 "<>"
 End Sub

• 200_success: You seem to be saying that this code was generated by the macro recorder, rather than written by you?

Answer 1

Avoid Select and Activate whenever possible. It is more efficient to reference the Objects directly then to work with then through the Selection method. Watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)

Instead of selecting the worksheet and then referencing the ActiveSheet

Sheets("Current Vacancies").Select
ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=1, Criteria1:="<>"

You can just refer to the ListObject directly by qualifying it by it's worksheet.

Sheets("Current Vacancies").ListObjects("Table4").Range.AutoFilter Field:=1, Criteria1:="<>"

Excel does not allow two ListObjects to share the same name so that the ListObjects range can be referenced directly by name.

Range("Table4").AutoFilter Field:=1, Criteria1:="<>"

Refactoring the code to avoid selecting the worksheets will give a significant performance boost.

Sub filteroutblanks()
 Range("TalentOutflow").AutoFilter Field:=1, Criteria1:="<>"
 Range("Table18").AutoFilter Field:=1, Criteria1:="<>"
 Range("InternalPromotions").AutoFilter Field:=1, Criteria1:="<>"
 Range("ExternalHires").AutoFilter Field:=1, Criteria1:="<>"
 Range("TalentInflow").AutoFilter Field:=1, Criteria1:="<>"
 Range("StatusExceptions").AutoFilter Field:=1, Criteria1:="<>"
 Range("Calibrations").AutoFilter Field:=1, Criteria1:="<>"
 Range("CurrentCDNorU").AutoFilter Field:=1, Criteria1:="<>"
 Range("LeaversTable").AutoFilter Field:=1, Criteria1:="<>"
 Range("DemotionsORexits").AutoFilter Field:=1, Criteria1:="<>"
 Range("Table4").AutoFilter Field:=1, Criteria1:="<>"
 Range("Languages").AutoFilter Field:=1, Criteria1:="<>"
 Range("Mobility").AutoFilter Field:=1, Criteria1:="<>"
End Sub

You could further simplify the code by iterating over an Array of table names

Dim item As Variant
For Each item In Array("TalentOutflow", "Table18", "InternalPromotions", "ExternalHires", "TalentInflow", "StatusExceptions", "Calibrations", "CurrentCDNorU", "LeaversTable", "DemotionsORexits", "Table4", "Languages", "Mobility")
 Range(item).AutoFilter Field:=1, Criteria1:="<>"
Next

Disabling Application.ScreenUpdating and setting Application.Calculation = xlCalculationManual will give you another speed boost.

Watch: Excel VBA Introduction Part 40 - Disabling Screen Updates

Sub filteroutblanks2()
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
 Dim item As Variant
 For Each item In Array("TalentOutflow", "Table18", "InternalPromotions", "ExternalHires", "TalentInflow", "StatusExceptions", "Calibrations", "CurrentCDNorU", "LeaversTable", "DemotionsORexits", "Table4", "Languages", "Mobility")
 Range(item).AutoFilter Field:=1, Criteria1:="<>"
 Next
 Application.Calculation = xlCalculationAutomatic
 Application.ScreenUpdating = True
End Sub