In <
[email protected]>, Dallman Ross <dman@localhost.>
spake thusly:
[Regarding a macro Dana DeLouis provided me with to hide AutoFilter
arrows on most columns, I'd then asked:]
Second, I already have a macro that gets called in the
same operation, which cycles through all the columns.
So I thought it would be economical and logical to combine
these functions. However, I can't get that to work, either.
I've worked on it and gotten it to work -- mostly. There
are two problems, for which I'm hoping for help.
The first problem is more serious, but the second is more
confusing.
Problem 1 (serious!): the "Range(whatever).Autofilter"
statement toggles the AutoFilter. If it was on already,
it turns it off. Then the rest of the macro fails with
an error. How do I just turn AutoFilter on, not toggle it?
Problem 2 (riddle): though I tell the AutoFilter to turn
off the arrow for all columns below Column 22, it leaves
Columns C and D with the arrow showing. Similarly, if
I tell it to turn off the arrow for columns > 3 and < 22,
it leaves the arrow showing for C, D, E, F, and G. What the
heck? I only want C to show.
By happenstance, columns D:G are hidden anyway, so
Problem 2 isn't very important right now. But I am baffled
by it and want to know the reason for the behavior!
Here's the macro.
=dman=
Option Explicit
---------------------------------------------------------------
Sub FilterHide()
Dim iCol As Long
Dim iLastCol As Long
Dim dummy As Range 'for AutoFilter
With Application.ActiveSheet
'// Find last column
iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'// Turn on AutoFilter for range
Range("C1:Y1").AutoFilter ' need this not to toggle!
Application.ScreenUpdating = False
For iCol = 1 To iLastCol
'// Turn off All Arrows between Columns 3 and 22
With .AutoFilter.Range
'If iCol > 1 And iCol < 22 Then
If iCol < 22 Then
Debug.Print iCol
.AutoFilter Field:=.Columns(iCol).Column, _
VisibleDropDown:=False
End If
End With
'// Hide columns with periwinkle interior color
If .Cells(2, iCol).Interior.ColorIndex = 24 Then
.Columns(iCol).Hidden = True
Else
.Columns(iCol).Hidden = False
End If
Next iCol
Application.ScreenUpdating = True
End With
End Sub
---------------------------------------------------------------