0

I have simple macro for hiding some rows in the spreadsheet: 

Set rngStat = ThisWorkbook.Worksheets("Instructions").Range("d4:d15")

With ThisWorkbook.Worksheets("Summary")
    Set rngToHide = Intersect(.Range("B:B"), .UsedRange).SpecialCells(xlCellTypeConstants)
End With
'Application.Calculation = xlCalculationManual
For Each cl In rngStat
    If cl.Offset(, 1).Value = "" Then

        For Each clH In rngToHide
            If UCase(cl.Value) = UCase(clH.Value) Then clH.EntireRow.Hidden = True
        Next clH
    End If
Next cl

It works, but execution takes centuries. So, I have preceded the loop with Application.Calculation = xlCalculationManual. Now, It's fast, but doesn't hide anything. I cannot understand, how setting calculation manual prevents Excel to hide rows. Do you have any ideas how should I hide rows without recalculating the spreadsheet every time?

Community
  • 1
  • 1
MarcinSzaleniec
  • 2,246
  • 1
  • 7
  • 22
  • Have you tried starting your code with `Application.ScreenUpdating = False` and then (at the end of your code just before the `End Sub`) `Application.ScreenUpdating = True`? – Ralph Oct 30 '17 at 09:58
  • Setting calc to manual should only affect that code if it depends on formula results that need to recalculate. You should switch your loops around though and use `Exit for` once you've hidden a row. – Rory Oct 30 '17 at 09:59
  • Can you try pressing F8 a few times with your code. `Application.Calculation = xlCalculationManual` should not have any impact on the hiding of the results. Unless if these `cl.Value` are some formulas. – Vityata Oct 30 '17 at 10:03
  • cl.value cannot have formula, because the range I am looping through has only cells with constants. Note .SpecialCells(xlCellTypeConstants) in my code. Also, adding ScreenUpdating to false is not the solution, because it is recalculation, not sreen flickering, that cause delay. – MarcinSzaleniec Oct 30 '17 at 10:05
  • In that case I'd recommend an array-based solution as shown in the solutions provided to this question: https://stackoverflow.com/questions/36873359/fastest-way-to-delete-rows-which-cannot-be-grabbed-with-specialcells This post is looking for a way to delete rows whereas you are merely trying to hide them. Yet, in terms of speed the approach is similar and these solutions should solve your problem. – Ralph Oct 30 '17 at 15:36

0 Answers0