1

Can someone help me make this more efficient? It is running around 10 seconds. Thanks much!

I've adapted this code to hide rows that contain "Hide" as the result of a formula in column A of my worksheet. 

Sub Hide_Rows()

 Application.Calculation = xlCalculationAutomatic
 Application.ScreenUpdating = False
 With ActiveSheet
For Each cell In .Range("a7:a115")
 If cell.Value = "Hide" Then
 cell.EntireRow.Hidden = True
 End If
 Next
 End With
 Application.ScreenUpdating = True
 End Sub

I have a similar Show_Rows sub that unhides the hidden rows. I have linked the button result (True or False) to cell A1 and then I'm using an If statement to either Hide_Rows or Show_Rows in the check box VBA

Private Sub CheckBox1_Click()
If Cells(1, 1).Value = True Then
     Hide_Rows
 Else
    Show_Rows
End If
End Sub
Joy
  • 11
  • 3
  • This runs in less than 1 second for me. It's hard to say what might be causing lag on your end, other than possibly some very complicated worksheet formulas if they are being re-calculated at runtime. – David Zemens Jun 23 '15 at 13:44
  • I do have a bunch of complicated formulas that could be causing the performance issue. Thanks for taking a look. – Joy Jun 23 '15 at 17:12
  • Try disabling the calculation at the beginning of the procedure, then re-enabling it at the end of the procedure. – David Zemens Jun 23 '15 at 17:18
  • I was able to do that, thanks much – Joy Jun 23 '15 at 17:21

1 Answers1

1

Your problem may be with lots of complicated formulas like David Zemens says. You have the right idea with the Application.Calculation but it should be Application.Calculation = xlCalculationManual at the start, and then Application.Calculation = xlCalculationAutomatic at the end.

User30923
  • 62
  • 5