Faster code than looping code

[Cammandk]

I have this code that does the job but can be slow as it's looping. Goes through 4000 lines.
Is there a faster solution - sure there is?

Sub HideBudgetRows()
Dim cel As Range
Dim rng As Range
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

  Set rng = Sheets("Budget").Range("A10", Range("EndOfBudgetRange"))
  For Each cel In rng
  If cel.Value = 0 Then
  cel.EntireRow.Hidden = True
 
  End If
 
  Next cel
 
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
 
End Sub

 

[Hui]

I don't think so

But will be interesting to see the responses

You can simplify the If statement to one line

Replace

  If cel.Value = 0 Then
  cel.EntireRow.Hidden = True
 
  End If

with:

  If cel.Value = 0 Then cel.EntireRow.Hidden = True

 

[Hui]

Have you tried Filtering?

Sub HideBudgetRows()
Dim cel As Range
Dim rng As Range
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

  Set rng = Sheets("Budget").Range("A10", Range("EndOfBudgetRange"))

  Rng.AutoFilter Field:=1, Criteria1:"<>0"

  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic

End Sub

You may have to adjust the code slightly to suit your range/data fields
Post your file if you want a hand

 

[Cammandk]

Thanks for this I'll try the filtering.
Have seen some code around that seems to select all the lines with "0" and then just deletes 1 time.
As you say I see what else might come back.
DK

 

[Debraj]

I have lil doubt on the name range..
May be it was dynamic and in each hidden in re-calculates..(you are hidding a single row "X" times, if the row contain "X" number of 0 (zero)

Can you give this a try..

Sub HideBudgetRows()
Dim cel As Range
Dim rng As Range
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

  Set rng = Sheets("Budget").Range(Range("A10"), Range("EndOfBudgetRange"))
  For i = 1 To rng.Rows.Count
  If Application.CountIf(rng.Rows(i), "0") Then
  rng.Rows(i).EntireRow.Hidden = True
  End If
  Next i
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

 

[Cammandk]

It's saying that there is a syntax error on this line?

Rng.AutoFilter Field:=1, Criteria1:"<>0"

 

[shrivallabha]

Little curious, how do you handle unhide? There's no code for it or is it always hidden rows will remain hidden?

 

[SirJB7]

Hi, Cammandk!
Hui yet gave you the simplest and fastest solution, Autofilter. Tried it yet?
Regards!

 

[Luke M]

Hi, Cammandk!
Hui yet gave you the simplest and fastest solution, Autofilter. Tried it yet?
Regards!

Perhaps you didn't see Cammandk's last post?

It's saying that there is a syntax error on this line?

Rng.AutoFilter Field:=1, Criteria1:"<>0"

@Cammandk
The line needs an "=" mark, replace with this:
Rng.AutoFilter Field:=1, Criteria1:="<>0"

 

[SirJB7]

@Luke M
Hi, buddy!
No, I haven't read it so carefully. Thanks for the catch.
Regards!