CODEWITHSUNDEEP
excelvbarowhide
Alby
Alby

Reputation: 53

Hiding rows based on cell value is very slow

I have working code to hide/unhide rows depending on the corresponding cell value.

This is a list of materials and there is a 'finalize' button. You press the button and any row where quantity = 0 should be hidden.

There are 400+ lines and I can see the lines disappear. It is processing roughly 20 lines per second which makes it over 20 seconds to do the list. The list will double every few months.

Is there another method that will hide the lines faster?

Hide:

Public Sub HideRows()
Dim cell As Range
For Each cell In ActiveSheet.Range("H18:H469")
    cell.EntireRow.Hidden = (cell.Value = 0 And cell.Value <> "")
Next cell
End Sub

Unhide:

Public Sub UnhideRows()
Dim cell As Range
For Each cell In ActiveSheet.Range("H18:H469")
    If (cell.Value = 0 And cell.Value <> "") Then cell.EntireRow.Hidden = False
Next cell
End Sub

Upvotes: 5

Views: 2113

Answers (2)

QHarr
QHarr

Reputation: 84465

I was just typing up as appeared in comments. Use Union to gather qualifying ranges and hide in one go. I am not sure why you are doing a double test. Wouldn't = 0 be sufficient? Or as @Marcucciby2 queries, did you intend an Or?

And as mentioned in other answer, you can do some optimization by switching of things like ScreenUpdating, pageBreaks and switch to manual calculation mode.

If possible, get rid of that ActiveSheet reference and use the actual workbook and worksheet references.

Option Explicit
Public Sub HideRows()
    Dim cell As Range, unionRng As Range
    For Each cell In ActiveSheet.Range("H18:H469")
        If cell.Value = 0 Then
            If Not unionRng Is Nothing Then
                Set unionRng = Union(unionRng, cell)
            Else
                Set unionRng = cell
            End If
        End If
    Next
    If Not unionRng Is Nothing Then unionRng.EntireRow.Hidden = True
End Sub

Upvotes: 10

ArcherBird
ArcherBird

Reputation: 2134

Turn off screen updating and manual calculations at the start of your code. Be sure to turn if back on at the end of your code.

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    '...your code...
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

Upvotes: 3

Related Questions