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?
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?