PDA

View Full Version : [SOLVED] Code Efficiency



Zack Barresse
04-13-2005, 08:45 AM
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
04-13-2005, 09:14 AM
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
04-13-2005, 09:15 AM
Ah ha!!! I knew I was overlooking something! Thanks Jake! :thumb

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

Jacob Hilderbrand
04-13-2005, 09:24 AM
You're Welcome :beerchug:

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.