Code Efficiency

[Zack Barresse]

I have some code that I am wondering if it is really the most efficient way to go or not. The reason I'm wondering is I was thinking it may go faster into an array than the way I'm currently doing it. This is the procedure ...

Sub HideCols()
    Dim c As Range
    If Intersect(Rows("6:6").SpecialCells(xlCellTypeConstants, 23), Rows("6:6").SpecialCells(xlCellTypeVisible)) Is Nothing Then Exit Sub
    For Each c In Intersect(Rows("6:6"), Rows("6:6").SpecialCells(xlCellTypeConstants, 23))
        Columns(c.Column).Hidden = True
    Next c
End Sub

(Yes, this code works as expected.) Basically it will check row 6. If any cells are (not hidden, and) not empty it will hide that entire column. The If/Intersect/Exit Sub line is to check for any blank/non-hidden columns before it runs. This is because there can be many cells in row 6 with values that I want to hide, and I didn't want this running the entire loop every single time I clicked the button associated with this procedure. So now I run it, then if I click the button again, it won't run the entire loop.

Any suggestions?

[Jacob Hilderbrand]

Is this what you want to accomplish?

Option Explicit

Sub HideCols()
Intersect(Rows("6:6"), Rows("6:6").SpecialCells( _
    xlCellTypeConstants, 23)).EntireColumn.Hidden = True
End Sub

You can also Union in the Special Cells Formulas as well.

[Zack Barresse]

Ah ha!!! I knew I was overlooking something! Thanks Jake!

Always great to get rid of the loop(s). I appreciate the help.

[Jacob Hilderbrand]

You're Welcome

Yeah, Loops are great, but very slow. It is best to avoid them whenever possible.

Now just to add a bit. Let's say you wanted to hide only certain values in the range. You would need an If statement within the loop, but you could still make it run faster.

Loop through the data and build a range, but do not hide anything yet. Then after the range is made, just hide the entire range in one shot.