I have 40 tabs in my workbook.
Each tab is an Income Statement
Each tab has about 300 rows of accounts with columns six wide of annual financial data (2020,2019,2018,2017,2016,2015)
Column A has a filter on it
Each row in column A has a formula that sums 2020 through 2015 for that row, if the sum doesn't equal zero (meaning there is financial data for this account) then the result is 1, if the sum does equal zero then the result is 0.
My macro goes through each tab, and filters based on Column A. The goal is to only display accounts that have financial data, and hide the rows that don't.
The financial data is using sumifs pulling from a table that itself is updating from an outside SQL database.
It takes several minutes for my macro to go through each tab and hide the Zero rows, is there any way to improve the speed?
This is my macro.
Sub Hide_Rows()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim ary As Variant
Dim sht As Variant
With Sheets("Macro")
ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
End With
For Each sht In ary
With Sheets(CStr(sht))
.Unprotect "password"
.Range("A1:A700").AutoFilter 1, "1"
.Protect "password"
.EnableSelection = xlUnlockedCells
End With
Next sht
Sheets("Control").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
Each tab is an Income Statement
Each tab has about 300 rows of accounts with columns six wide of annual financial data (2020,2019,2018,2017,2016,2015)
Column A has a filter on it
Each row in column A has a formula that sums 2020 through 2015 for that row, if the sum doesn't equal zero (meaning there is financial data for this account) then the result is 1, if the sum does equal zero then the result is 0.
My macro goes through each tab, and filters based on Column A. The goal is to only display accounts that have financial data, and hide the rows that don't.
The financial data is using sumifs pulling from a table that itself is updating from an outside SQL database.
It takes several minutes for my macro to go through each tab and hide the Zero rows, is there any way to improve the speed?
This is my macro.
Sub Hide_Rows()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim ary As Variant
Dim sht As Variant
With Sheets("Macro")
ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
End With
For Each sht In ary
With Sheets(CStr(sht))
.Unprotect "password"
.Range("A1:A700").AutoFilter 1, "1"
.Protect "password"
.EnableSelection = xlUnlockedCells
End With
Next sht
Sheets("Control").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub