-1
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ You seem to be saying that this code was generated by the macro recorder, rather than written by you? \$\endgroup\$ – 200_success Jan 29 '18 at 3:51
0
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.