Help me speed up my code

[ML43s52]

[FONT=&quot]First of all I would like to thank you for taking your time to read my post, and preferably providing me with a solution to my problem, any input or assistance will be greatly appreciated. [/FONT]
I have read several posts about the importance of minimizing traffic between VBA and excel, the importance of avoiding any unnecessary reference to a worksheet once the macro has begun, the importance of a[FONT=&quot]voiding reading or writing worksheet data within loops whenever possible, [/FONT]and reading and writing blocks of data in a single operation to speed up my code, but I have been unsuccessful at implementing any of these ideas.

Below is what I have so far, the code works but it seems to take forever to complete the task at hand, forever being 5 seconds or so.

Cell "AU6015" is the sum of all values in column "AU", this macro is triggered whenever the value of cell "AU6015" changes.
If the value of cell "AU1" is equal to 0 (zero) then row 1 is hidden, if cell "AU1" is not equal to 0 (zero) then row 1 is not hidden, the same holds true for rows 2 through 6015.

Private Sub Worksheet_Calculate()
Static oldval
If Range("AU6015").Value <> oldval Then
oldval = Range("AU6015").Value

screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False


Dim i As Long
For i = 1 To 6014 Step 1
Rows(i).EntireRow.Hidden = (Range("AU" & i).Value = 0)

Next
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True




End If
End Sub
[FONT=&quot]
[/FONT]

 

[shg]

How about ...

Private Sub Worksheet_Calculate()
  Static vOld As Variant

  If Range("AU6015").Value <> vOld Then
    vOld = Range("AU6015").Value
    Application.EnableEvents = False
    Range("AU1:AU6014").AutoFilter Field:=1, Criteria1:="<>0"
    Application.EnableEvents = True
  End If
End Sub

 

[ML43s52]

How about ...

Private Sub Worksheet_Calculate()
  Static vOld As Variant

  If Range("AU6015").Value <> vOld Then
    vOld = Range("AU6015").Value
    Application.EnableEvents = False
    Range("AU1:AU6014").AutoFilter Field:=1, Criteria1:="<>0"
    Application.EnableEvents = True
  End If
End Sub

Thank you for the quick reply and suggestion, I copied and pasted your code exactly as you proposed, the trigger for the macro is working, the values in column "AU" are changing to zeros but it doesn't hide any of the rows that contain a zero in column AU.

 

[Rick Rothstein]

[FONT="]Cell "AU6015" is the sum of all values in column "AU", this macro is triggered whenever the value of cell "AU6015" changes.
If the value of cell "AU1" is equal to 0 (zero) then row 1 is hidden, if cell "AU1" is not equal to 0 (zero) then row 1 is not hidden, the same holds true for rows 2 through 6015.

There is a small chance that there is a quick way to do what you want (other than the AutoFilter shg suggested, which should work), but it depends on your worksheet's layout. First, can I assume that cells AU1:AU6014 contain formulas which are adding up values in other cells on each respective row (if so, show us the formula for AU1)? If so, can any of the numbers those formulas are adding together ever be negative? If not, then what is in the cells when column AU is 0 for a particular row... zeroes are blank cells?

 

[ML43s52]

There is a small chance that there is a quick way to do what you want (other than the AutoFilter shg suggested, which should work), but it depends on your worksheet's layout. First, can I assume that cells AU1:AU6014 contain formulas which are adding up values in other cells on each respective row (if so, show us the formula for AU1)? If so, can any of the numbers those formulas are adding together ever be negative? If not, then what is in the cells when column AU is 0 for a particular row... zeroes are blank cells?

Actually, cells AU2:AU6014 contain the formulas........

AU2=SUM(K2:V2)*$AT$1
AU3=SUM(K3:V3)*$AT$1
AU4=SUM(K4:V4)*$AT$1

There will never be negative numbers.

$AT$1 is triggered by a check box function, toggles between 0 & 1