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

 

[davlcam]

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

Bam! Microseconds. Thank you sir.

" I stand here tonight as excited as a masochist who has just been arrested by the Spanish Inquisition. " ~ Black Adder

 

[JackDanIce]

Glad it helped. Made a slight change to include a variable, k that you can change if you want values other than 0 (see bits in red)

Sub FillEmpty()

Dim ws As Worksheet
Dim i As Long, j As Long, k As Long
Dim msg As String
Dim iRange As Range

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

k = 0

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
Cells.EntireRow.Hidden = False

j = Range("B3:B100").Find(what:=k).Row
Set iRange = Range("B3:B100").Find(what:=k)

For i = j + 1 To 100
    If Range("B" & i) = k 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

 

[davlcam]

On second review, noticed 2 things.

Cell B5 with a value of 0 does not hide.

Thinking

For i= j+1 to 100

Should actually be

For i=j+ 0 to 100

For my test data -

In Cell B22 and B23, I have positive values, with the cells hidden- before i run the code. The code does not unhide them. This simulates the scenario where: the previous lookup used to set the value of the B cells showed a 0 in B22 and B23, but the new lookup has resulted in non zero values now in those cells, and the code should now unhide those cells.

 

[JackDanIce]

The line in blue should be unhiding all the rows on the sheet before it searches the rows in column B for the ones that contain 0 that you want to hide. Press F8 to step through the code and see if B22 and B23 are unhidden (or not)

Anyway, I've set up a second union range for values that are not 0 and then put in code to set any rows of this second union to not be hidden

Also just change

For i = j + 1 to 100
to
For i = j to 100

Updated:

Sub FillEmpty()

Dim ws As Worksheet
Dim i As Long, j As Long, k As Long
Dim msg As String
Dim iRange As Range, jRange As Range

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

k = 0

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
Cells.EntireRow.Hidden = False

j = Range("B3:B100").Find(what:=k).Row
Set iRange = Range("B3:B100").Find(what:=k)
Set jRange = Range("B1")

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

iRange.EntireRow.Hidden = True
jRange.EntireRow.Hidden = False

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

End Sub

 

[davlcam]

That last bit got it.

I saw your unhide all line, but for some reason on the F8 step through it didn't do anything.

Works fine now tho. Thanks again.

Good bit of code to archive possibly. Ive seen a few posts on best way to hide unhide rows when a value is zero, (where the cells referenced are dynamic) and this is by far the fastest i've seen.