Auto-filter question

[Dallman Ross]

Hi. I am a little confused about where the AutoFilter should be.
Is it C:Y ??
Yes.

The reason I ask is that it appears iLastCol should not be
necessary. Note that it appears you want to Filter on C:Y, but
the loop goes from Column 1 to iLastCol (We don't know where
iLastCol is.)

I understand what you're saying (I think), but the reason I loop
from Column 1 to iLastCol is so I can combine two actions in one
macro. There were two macros originally: one cycled through all
columns looking for some we want to hide. The other cycled through
(some) columns looking for AutoFilter arrows to turn off. My
premise was, it seemed silly to call two separate macros to
cycle through columns and do Task A or Task B; I decided to
combine the macros and cycle through columns only once.

So that's why we start at 1 instead of 3 and go all the way
to iLastCol.

But then I put in an If-Statement to bother with the AutoFilter
part of the tasks (i.e., Task B above) only if iLastCol is in the
range I care about. (Then I took out the "> 3" part in testing,
because it was not doing what I expected in any case. Now I've
put that back for publishing here below.)

Btw, the VB/VBA terminology in general confuses me. Maybe
someone can explain what, exactly, a "method" is and what
exactly an "object" is. When I go to the Excel VBA Help
pages and try to read about AutoFilter, for example, I'm
asked if I want "method" or "object." I have no idea!

I wish the creators of these goofy visual languages had
stuck to normal old terminology (that I understand!)
like "expression," "statement," "action," "command," and
"assignment." Anyway, when I write my questions here,
I have been having to rein in my expressiveness to avoid
using the words I don't understand. ;-) So I've been
talking about "statement" when I've had no idea if I
should be saying "method," for example.


Here's the macro again as it is now, still with Problem 2
(iCol range acts goofy turning off AutoFilter).

As long as you're looking, please tell me if my brainstorm method
(English "method," not VBA "method" -- and I sort of am annoyed that
these newfangled visual-based languages co-opt perfectly good
English words I wish to use when talking about coding) for getting
the range thing to work ("Dim dummy as Range") is kosher, or,
if not, what I should do instead to get the #$%# code to work?

Option Explicit
Sub FilterHide()

Dim iCol As Long
Dim iLastCol As Long
Dim dummy As Range 'for AutoFilter

With Application.ActiveSheet

'// Remove AutoFilter if On
If .AutoFilterMode Then .AutoFilterMode = False

'// Turn on AutoFilter for range
Range("C1:Y1").AutoFilter

'// Find last column
iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Application.ScreenUpdating = False
For iCol = 1 To iLastCol

'// Turn off All Arrows between Columns 3 and 22
With .AutoFilter.Range

'// below If-statement results in some unexplained wonkiness
If iCol > 3 And 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


=====================

 

[Dallman Ross]

Hi. I am a little confused about where the AutoFilter should be.
Is it C:Y ??
The reason I ask is that it appears iLastCol should not be
necessary. Note that it appears you want to Filter on C:Y, but
the loop goes from Column 1 to iLastCol (We don't know where
iLastCol is.)

I suppose I wrote too much before. I notice around here that
when I write long answers, I get no or few replies. :-/
I should remember that brevity is the soul of wit.

Dana, I appreciate your input. It has been very helpful.
Oh, and: Happy Thanksgiving (before I forget).

I've now had the time to go back and test further. Here
is what I find:

If I try something like:

Range("C1:Y1").AutoFilter

For iCol = 3 To 21
With .AutoFilter.Range
.AutoFilter Field:=.Columns(iCol).Column, _
visibledropdown:=False
End With
Next


then Columns C, D, E, and F all have an arrow. Don't ask me why.

If I change the For-statement to

For iCol = 1 To 21

then columns C and D retain an arrow. Again, don't ask me why.

If I extend the upper bound of the loop to 22, then I get a
runtime error.

If I go with your original suggestion, all works fine.
I sure would like to know why the selected bounds don't
work as expected, though.

If I set the original range for AutoFilter to this:

Range("A1:Y1").AutoFilter '// A1 instead of C1

Then this works:

For iCol = 1 To 25 '// 25 instead of 21
With .AutoFilter.Range
Debug.Print iCol
.AutoFilter Field:=.Columns(iCol).Column, _
visibledropdown:=False

'// But have Drop down arrow for Column C
.AutoFilter Field:=3, visibledropdown:=True
End With
Next


So starting the autoFilter after the first column leads
to problems with controlling the arrows via a loop.

At this stage, it seems easiest just to go with your original.
I'll post what works again at the bottom here.

-dman- (would still like to know if my "dummy" Dim Range is clean enough)


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

'// Remove AutoFilter if On
If .AutoFilterMode Then .AutoFilterMode = False

'// Turn on AutoFilter for range
'Range("a1:Y1").AutoFilter
[A1].CurrentRegion.AutoFilter

Application.ScreenUpdating = False

For iCol = 1 To iLastCol
With .AutoFilter.Range
Debug.Print iCol
.AutoFilter Field:=.Columns(iCol).Column, _
visibledropdown:=False

'// But have Drop down arrow for Column C
.AutoFilter Field:=3, visibledropdown:=True
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