Hide rows macro

[eksplosion310]

I do not have much VBA knowledge but I put together a macro to hide rows that meet certain criteria (Module 2 in the sample provided). I am running into a problem where it takes a considerable long amount of time to run through about 1min and i know it could be simplified to run almost instant. No matter how many rows need to be hidden, meeting the criteria, the macro still takes the same amount of time. After many failed attempts I couldn't make it work. If anyone could shine some light on this and see how the code could be rewritten to work quicker.

Sub HideY()
Dim LastRow As Long, c As Range

Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

LastRow = Cells(Cells.Rows.Count, "Y").End(xlUp).Row
On Error Resume Next
For Each c In Range("Y10:Y1000")
   If c.Value = 1 Then
        c.EntireRow.Hidden = True
    ElseIf c.Value = 2 Then
        c.EntireRow.Hidden = False
    End If
Next
On Error GoTo 0

Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True

Set dbsTemp = Nothing
End Sub

 

[vletm]

eksplosion310
add
Application.ScreenUpdating = false
before Application.EnableEvents = false
and
Application.ScreenUpdating = true
after Application.EnableEvents = true
>>
Test again

 

[eksplosion310]

Still takes almost a min.

 

[vletm]

eksplosion310
Do someone like formulas?
Now 5sec hide
and
maybe Unhide would be quicker too?

 

[Marc L]

a macro to hide rows that meet certain criteria

The best is not to use looping in general VBA
but inner Excel feature like a filter or an advanced filter, …
​

 

[eksplosion310]

The best is not to use looping in general VBA
but inner Excel feature like a filter or an advanced filter, …
​

Was there looping in the code I provided?

 

[eksplosion310]

eksplosion310
Do someone like formulas?
Now 5sec hide
and
maybe Unhide would be quicker too?

WOW, thank you so much that works so much better.
Could you tell me what is going on in this part, I cant seem to understand? ALso, why y=10?

Range("A10:A" & LastRow).RowHeight = 0
    For y = 10 To LastRow
        If Cells(y, 25) <> 1 Then Cells(y, 25).RowHeight = 20
    Next y

 

[vletm]

eksplosion310 - hmm? - okay
Because You maybe have 'dropdown-data' after rows 1000,
this would be one way to do this.
Range: it sets 'all' row 'hidden' ... RowHeight = 0
10: Your 1st data row is 10
For ... Next: tests the Y-column value and sets rowheight to 20 if not 1
... and same logic with 'ShowY' ... next Sub
> If those 'dropdown-data' would be different ... better place then those would be more quicker.
>> Of course, with 'Filter' many things would be easier,
but I didn't want to change too much!

 

[Marc L]

Was there looping in the code I provided?

YES ‼
​

 

[eksplosion310]

was it because of this?

ForEach c In Range("Y10:Y1000")

 

[Marc L]

Yes as you can check in VBA inner help …

So in this case an easy filter do quickly the job via one statement !
​

 

[vletm]

eksplosion310
I tested 'Filter'.
It works quick and so on,
BUT then You would need to change layout of You sheet ...
... and those 'Filter' could operate with 'Buttons' too.

 

[Marc L]

Other way in case of data sorted in criteria column :
Find first and last rows to hide then hide the block at once …​