How can I speed this up?

[davlcam]

This runs really slow. Any ideas on how to speed it up - its a toggle, when the values change from 0 to 1, the cells will hide / unhide. But it runs painfully slow.

Private Sub test99()

Application.ScreenUpdating = False

For Each Cell In Range("B5:B100")
If Cell.Value = 0 Then
Cell.EntireRow.Hidden = True
' End If
Else
' If Cell.Value > 0 Then
Cell.EntireRow.Hidden = False
End If
Next Cell

Application.ScreenUpdating = True

End Sub

 

[wigi]

With "If Cell.Value = 0 Then", are you looking for empty cells perhaps?

 

[dave3009]

Try

Sub test99()

Application.ScreenUpdating = False
For i = 5 To 100
    
    With Range("B" & i)
        Select Case .Value
            Case 0: .EntireRow.Hidden = True
            Case Else: .EntireRow.Hidden = False
        End Select
    End With
Next i
Application.ScreenUpdating = True

End Sub

 

[wigi]

I do not see any speed advantage Dave, although I did not test your approach.

 

[davlcam]

Thanks dave, but no speed advantage.

I counted each, using the tried and true One-OneThousand, Two-Onethousand.... method.

No change in speed.

Each ran about 5-6 seconds.

@ Wigi - no not empty cells, cells with a value of Zero

 

[dave3009]

There must be another issue, looping through 100 cells is a fraction of a second job, and using Excels timer for the test there is a small advantage to the Select Case technique

 

[davlcam]

Got it down to around 3 seconds by ensuring values in cells were an integer between 0-10. Still seems silly slow for even that.

Dif between code 1 and code 2 is marginal, (Let me know what timer you are using?)

Could just be 2007s slower VBA engine???

 

[JackDanIce]

I'm not happy with how I initialise the Union of the ranges, but otherwise, it's reasonably fast:

Sub PiPiForMy ()


Dim i As Long, j As Long
Dim iRange As Range

With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

j = Range("B5:B100").Find(what:="1").Row
Set iRange = Range("B5:B100").Find(what:="1")

For i = j + 1 To 100
    If Range("B" & i) = 1 Then
        Set iRange = Union(iRange, Range("B" & i))
    End If
Next i

iRange.EntireRow.Hidden = True

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With


End Sub

Lots of things could be improved in above, so look forward to reading suggestions

 

[davlcam]

I'm not happy with how I initialise the Union of the ranges, but otherwise, it's reasonably fast:

Sub PiPiForMy ()


Dim i As Long, j As Long
Dim iRange As Range

With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

j = Range("B5:B100").Find(what:="1").Row
Set iRange = Range("B5:B100").Find(what:="1")

For i = j + 1 To 100
    If Range("B" & i) = 1 Then
        Set iRange = Union(iRange, Range("B" & i))
    End If
Next i

iRange.EntireRow.Hidden = True

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With


End Sub

Lots of things could be improved in above, so look forward to reading suggestions

This seems to hide all cells with a value of 1. 0's dont hide. Sort of the opposite of the routine's intent.

Value for B can be any integer from roughly 0-30. (This variable changes by a lookup, which the user selects.)

The code will fire after the user selects the lookup that will change the values of b5:b100

 

[JackDanIce]

Late, tired and not paying attention, try:

Sub PiPiForMy ()


Dim i As Long, j As Long
Dim iRange As Range

With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

j = Range("B5:B100").Find(what:="0").Row
Set iRange = Range("B5:B100").Find(what:="0")

For i = j + 1 To 100
    If Range("B" & i) = 0 Then
        Set iRange = Union(iRange, Range("B" & i))
    End If
Next i

iRange.EntireRow.Hidden = True

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With


End Sub

See bits in red where I've changed my code