Increase Speed of Hide Rows with 0 Value Macro

[PandoraGirl]

I have a spreadsheet where I retrieve almost 200 lines of data from another program. I have created the following macro that will hide all rows where the value of the cells are 0. The macro works but I think it takes too long. Can you help me speed it up? I have done extensive research on various forums. I added the .EnableEvents, ActiveSheet.DisplayPageBreaks and .Calculations lines based off that research but it still is not any faster. I am running Excel 2013 on a Windows 7 machine.

Thank you in advance!

Dim ActRow As Integer
Dim Hide As Long

With Application
    .ScreenUpdating = False
    .EnableEvents = False
     ActiveSheet.DisplayPageBreaks = False
    .Calculation = xlCalculationManual
End With
Range("B9:Y200").Select
    For Each cell In Selection
      ActRow = ActiveCell.Row
        Hide = Application.WorksheetFunction.Sum(Range("B" & ActRow & ":Y" & ActRow))
        If Hide = 0 Then
            ActiveCell.EntireRow.Hidden = True
                Else
        End If
ActiveCell.Offset(1, 0).Activate
    Next cell
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With
    Rows("184").EntireRow.Hidden = False
End Sub

 

[Fluff]

Try

Dim Cell As Range
Dim Hide As Long

With Application
    .ScreenUpdating = False
    .EnableEvents = False
     ActiveSheet.DisplayPageBreaks = False
    .Calculation = xlCalculationManual
End With
    For Each Cell In Range("B9:B200")
        Hide = Application.WorksheetFunction.sum(Cell.Resize(, 24))
        If Hide = 0 Then
            Cell.EntireRow.Hidden = True
        End If
    Next Cell
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With
    Rows("184").EntireRow.Hidden = False
End Sub

 

[PandoraGirl]

Thank you! This is MUCH faster. I'm new to VBA, would you mind telling me the logic in the changes you made?

Thanks again!

 

[Fluff]

The main difference,as far as speed is concerned, is that you are looking at every cell in the range B9:Y200, whereas my suggestion is only looking at B9:B200.
With your code you'll look at B9 & see if that row totals 0, you'll then look at C9 & do the same & then D9, E9 etc. In effect you'll check if row 9 totals 0, 24 times.

 

[PandoraGirl]

Thank you for the explanation. I thought my Macro was looking to see if there was a number in each cell as opposed to if the row totaled 0. I can see why this would speed things up.

 

[Fluff]

Glad to help & thanks for the feedback