Hiding Columns SLOW

[Gregg Wolin]

I'd like to toggle the visibility of columns based on values in a header row where the cells either have a 0 or 1. I use the following code (to hide) that works - but its SLOW (on a fast machine). It also doesn't deal with the unhide part of the toggle (which i held off trying until i figured out the speed thing).

Sub Toggle_ZeroColumnsMonthly()
Dim c As Range
    For Each c In Range("a1:nd1").Cells
        If c.Value = "0" Then
            c.EntireColumn.Hidden = True
              Else
            c.EntireColumn.Hidden = False
       End If
    Next c
End Sub

Any thoughts would be appreciated

 

[Marc L]

Before the loop : Application.ScreenUpdating = False​

​

After the loop : Application.ScreenUpdating = True​

​

 

[Gregg Wolin]

Thanks for the quick reply - unfortunately, that didn't help the performance. Takes like 4s seconds to hide the columns.

 

[vletm]

Gregg Wolin
1st set ALL columns to False
and
after that set ONLY those 0's to True.
((( are those "0" or numbers 0 ? )))

Of course, sometimes even 4sec could be fast too - how hurry?

 

[Fluff13]

I'm surprised it takes 4seconds, unless something else is going on.
How long does this take you. It takes 0.2secs for me.

Sub GregWolin()
    Dim Cl As Range
    For Each Cl In Range("A1:ND1")
        Cl.EntireColumn.Hidden = Cl.Value = "0"
    Next Cl
End Sub

 

[Gregg Wolin]

Gregg Wolin
1st set ALL columns to False
and
after that set ONLY those 0's to True.
((( are those "0" or numbers 0 ? )))

Of course, sometimes even 4sec could be fast too - how hurry?

Number 0
No improvement in speed.

 

[Gregg Wolin]

I'm surprised it takes 4seconds, unless something else is going on.
How long does this take you. It takes 0.2secs for me.

Sub GregWolin()
    Dim Cl As Range
    For Each Cl In Range("A1:ND1")
        Cl.EntireColumn.Hidden = Cl.Value = "0"
    Next Cl
End Sub

Still 4 seconds+

 

[Gregg Wolin]

Still 4 seconds+

I'm surprised it takes 4seconds, unless something else is going on.
How long does this take you. It takes 0.2secs for me.

Sub GregWolin()
    Dim Cl As Range
    For Each Cl In Range("A1:ND1")
        Cl.EntireColumn.Hidden = Cl.Value = "0"
    Next Cl
End Sub

I copied the worksheet, formats and contents (not formulas) to a new workbook and it runs fast.
I added "Application.EnableEvents = False/True" before/after the loop but that didn't help

 

[Fluff13]

What happens if you put Application.Calculation = xlCalculationManual at the start & Application.Calculation = xlCalculationAutomatic at the end?

Also is row 1 hard values, or a formula?

 

[vletm]

Gregg Wolin
Create a sample file as Your real file with all used feature (including ALL macros)
... of course, with sample values and send it here.