Simple Script to hide rows - runs slow

Hi folks! I have been searching the forum for a solution to this problem, but I'm stumped. I have a simple code that that hides a range of rows if a script is called. The script "CallCompConv" is called if a selection is made on a drop down menu. The problem is, it hides the rows just fine, but stalls for 30 seconds to a minute! I'm stumped, because it all seems like a simple script! I have heard that scripting it with autofilter may work better, but I have not been able to find that code. Any help would be greatly appreciated!

Sub CallCompConv()

Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
Application.EnableEvents = False
ActiveWorkbook.Sheets("Customer Setup").Activate

ActiveSheet.Rows("51:57").EntireRow.Hidden = True
ActiveSheet.Rows("46:50").EntireRow.Hidden = False

ActiveSheet.DisplayPageBreaks = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

This is the code that the dropdown uses to call the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A35")) Is Nothing Then
Select Case Range("A35")

Case "Competitive Conversion"
Call CallCompConv

Case "New Location Bodyshop"
Call CallNewLoc

Case "Existing Bodyshop"
Call CallExistingBS

Case "New Jobber"
Call CallNewJobber

Case "Existing Jobber"
Call CallExistingJobber

End Select
End If

End Sub

Thanks! If that is the case (running the worksheet_change over and over), any tips on how to stop that or move the "call" script to a different location?

Would this be an option? Rather than calling the subs incorporate the action in your event?

Please Login or Register  to view this content.

Unfortunately it is just as slow doing it that way. Thanks though!