Speeding up some slow code

[jUStPunkin]

I have a spreadsheet that I have protected (simply so that people don't change my formulas, either intentionally or accidentally). As I'm not as concerned with the intentional changes, I don't have it password protected, just protected.

In any case, on a particular worksheet, I give people a choice via a dropdown box. Based on their chose, I hide certain rows on the worksheet.

Back in access 2003, it worked fine. Now, in 2007, every single time someone changes any cell value on that worksheet, it takes forever. What I believe is happening is that it is running that hide/unhide code that I have written.

Can anyone offer some advice on how to speed this up - changes to the code, a better way to do what I want, etc.

Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets("spouts").Unprotect
    Select Case Worksheets("spouts").Range("$G$3")
        Case Is = "Holes"
            Worksheets("spouts").Range("$A$44:$N$67").EntireRow.Hidden = True
            Worksheets("spouts").Range("$A$26:$N$42").EntireRow.Hidden = False
        Case Is = "Slots"
            Worksheets("spouts").Range("$A$27:$N$29").EntireRow.Hidden = True
            Worksheets("spouts").Range("$A$34:$N$42").EntireRow.Hidden = True
            Worksheets("spouts").Range("$A$47:$N$50").EntireRow.Hidden = True
            Worksheets("spouts").Range("$A$44:$N$46").EntireRow.Hidden = False
            Worksheets("spouts").Range("$A$51:$N$67").EntireRow.Hidden = False
            
        Case Else
            Worksheets("spouts").Range("$A$27:$N$67").EntireRow.Hidden = False
    End Select
   Worksheets("spouts").Protect
End Sub

Thanks,
Brenda

 

[vaskov17]

Try turning off automatic calculation of formulas and set it to manual.

 

[jUStPunkin]

Thanks for the suggestion, but that didn't help. I'm pretty sure it's the hiding/unhiding that is causing the slow down. If I comment out that code, it acts the way I expect it to act, with no slow down at all.

Thanks,
Brenda

 

[wigi]

You should limit the Change event to the particular cell:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Worksheets("spouts")
        If Target.Address = "$G$3" Then
            Application.ScreenUpdating = False
            .Unprotect
            Select Case Target.Value
            Case "Holes"
                .Rows("44:67").Hidden = True
                .Rows("26:42").Hidden = False
            Case "Slots"
                .Rows("27:29").Hidden = True
                .Rows("34:42").Hidden = True
                .Rows("47:50").Hidden = True
                .Rows("44:46").Hidden = False
                .Rows("51:67").Hidden = False
            Case Else
                .Rows("27:67").Hidden = False
            End Select
            .Protect
        End If
    End With
End Sub

Also, use the With...End With statement to increase the maintainability of your code.

 

[jUStPunkin]

Excellent! Thank you so very much. This worked perfectly!

 

[wigi]

You're welcome, thanks for testing and providing feedback.