• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hiding Columns SLOW

Gregg Wolin

Member
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).

Code:
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

Excel Ninja
Before the loop : Application.ScreenUpdating = False
After the loop : Application.ScreenUpdating = True
 

Gregg Wolin

Member
Thanks for the quick reply - unfortunately, that didn't help the performance. Takes like 4s seconds to hide the columns.
 
Last edited by a moderator:

vletm

Excel Ninja
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

Active Member
I'm surprised it takes 4seconds, unless something else is going on.
How long does this take you. It takes 0.2secs for me.
Code:
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

Member
I'm surprised it takes 4seconds, unless something else is going on.
How long does this take you. It takes 0.2secs for me.
Code:
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

Member
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.
Code:
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

Active Member
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?
 
Top