Filter tables in the workbook

[Celticfc]

Hi all,

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

My code is below, thank you in advance.

Sub FilterOutBlanks()
'
' FilterOutBlanks Macro
'
'

    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

 

[Paul Ked]

There is no need for the Select, which eats up time, try:

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