Hello,
I'm running a loop macro to hide (or unhide) all rows in a range based on criteria in a cell. When I first open the spreadsheet and run "hide" or "unhide" macro (identical except for "yes" or "no" result), it goes fast, but if I try running it again, it slows down significantly. I don't have any filters or conditional formats that would slow it down perhaps.
I'm running a loop macro to hide (or unhide) all rows in a range based on criteria in a cell. When I first open the spreadsheet and run "hide" or "unhide" macro (identical except for "yes" or "no" result), it goes fast, but if I try running it again, it slows down significantly. I don't have any filters or conditional formats that would slow it down perhaps.
Code:
Sub Hide_Detail()
Dim Areas As Range
Dim Cell As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayAlerts = False
.ErrorCheckingOptions.InconsistentTableFormula = False
End With
On Error GoTo ErrorHandler
Set Areas = Sheets("Settings").Range("V1:V10")
For Each Cell In Areas
Sheets(Cell.Value).Activate
Call Calculate_HideShow_Condition
Call Hide_Rows
Application.GoTo Reference:=Range("L1"), Scroll:=True
ActiveSheet.ResetAllPageBreaks
Rows("56:56").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Rows("211:211").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Rows("428:428").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Rows("493:493").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Next Cell
ErrorHandler:
Sheets("Settings").Activate
Set Areas = Nothing
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.DisplayAlerts = True
End With
ThisWorkbook.Save
End Sub
- Call Calculate_HideShow_Condition:
Code:
Sub Calculate_HideShow_Condition()
Dim i As Integer
Dim rng As Range
For i = 4 To 753
Set rng = Range("K" & i)
rng.FormulaR1C1 = _
"simple formula here to return a Yes or No"
rng.Value = rng.Value
Next i
Set rng = Nothing
End Sub
- Call Hide_Rows:
Code:
Sub Hide_Rows()
Dim i As Integer
With Activesheet
For i = 4 To 753
Select Case .Range("K" & i).Value
Case "Yes"
.Rows(i & ":" & i).EntireRow.Hidden = True
End Select
Next i
End With
End Sub
Last edited: