Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one
mix advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-

Try inserting a column on either side ot the one you want to filter, apply
the Autofilter to that column then delete the two empty columns you've just
inserted.
Regards,
Alan.

"Dallman Ross" <dman@localhost. wrote in message
...
Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one
mix advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-

Not sure this is what you want, but

If you want to hide all arrows, go to

Tools=Options=View and under the objects entry, select hide all.

--
Regards,
Tom Ogilvy


"Dallman Ross" <dman@localhost. wrote in message
...
Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one
mix advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-

If you want to apply a filter to one column, select the column or cells in
the column and apply the autofilter (assuming it is not currently applied).

--
Regards,
Tom Ogilvy


"Dallman Ross" <dman@localhost. wrote in message
...
Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one
mix advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-

In , Tom Ogilvy
spake thusly:

Not sure this is what you want, but

If you want to hide all arrows, go to

Tools=Options=View and under the objects entry, select hide
all.

Interesting. I want most of them, though. Only want to hide
a subset of the AutoFilter arrows. (I'm using Excel 2002
under XP Pro.)

I know I can have the arrows only on a set range. E.g.,
if my sheet has columns A-Z but I highlight only columns
C-E, then select Data - Filter - AutoFilter, the
pull-down arrows are only there for those columns.
That's fine as far as it goes.

The range apparently has to be contiguous. So I can't
just highlicht columns C, H, and Y and have the arrows
visible there only. All columns between C and Y also
get arrows when I try to turn on AutoFilter for that
range.

If only C, H, and Y had arrows, I'd be happy enough.
However, I'm only setting a static filter choice once
in Columns H and Y -- the rest of the time, I'm only
filtering with Column C. So ideally, I'd like only to
see a pull-down arrow for Column C, but have Columns
H and Y retain the filtering that I've given them.

-dman-

In , Tom Ogilvy
spake thusly:

If you want to apply a filter to one column, select the column or
cells in the column and apply the autofilter (assuming it is not
currently applied).

Please do see my answer to you other posted article in the thread.
Thanks, Tom.

-dman-

In , Alan
spake thusly:

Try inserting a column on either side ot the one you want to
filter, apply the Autofilter to that column then delete the two
empty columns you've just inserted.

Hmm.

Alan, I can't get that one to work. Excel 2002 under XP Pro.

-dman-

=============================================
"Dallman Ross" <dman@localhost. wrote in message
...

Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one mix
advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-

In , Dallman Ross <dman@localhost.
spake thusly:

The range apparently has to be contiguous. So I can't
just highlicht columns C, H, and Y and have the arrows
visible there only. All columns between C and Y also
get arrows when I try to turn on AutoFilter for that
range.

If only C, H, and Y had arrows, I'd be happy enough.
However, I'm only setting a static filter choice once
in Columns H and Y -- the rest of the time, I'm only
filtering with Column C. So ideally, I'd like only to
see a pull-down arrow for Column C, but have Columns
H and Y retain the filtering that I've given them.

And now I've found taht I only really need C and Y.
Y, I only need to set once and forget. But C gets
interactively filtered.

-dman-

If you only want to see one arror--only apply data|filter|autofilter to that
single column. (I don't see the point of autofiltering 12 columns and then
hiding 11 arrows.)

If you want to see multiple arrows, but not in contiguous columns, you can use
the technique at Debra Dalgleish's site:

http://contextures.com/xlautofilter03.html#Hide




Dallman Ross wrote:

Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one
mix advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-

--

Dave Peterson

In , Dave Peterson
spake thusly:

If you only want to see one arror--only apply
data|filter|autofilter to that single column. (I don't see the
point of autofiltering 12 columns and then hiding 11 arrows.)

I want to see one arrow in Column C, but I want to filter a
second column way over in Column Y, but only statically; not
interactively. Column C gets the interactive filter. So it's the
only arrow I want to see. It wouldn't be tragic if there was an
arrow on Column Y too, though.

If you want to see multiple arrows, but not in contiguous
columns, you can use the technique at Debra Dalgleish's site:

http://contextures.com/xlautofilter03.html#Hide

I saw that earlier (before asking here), and tried it; but
it seemed to me not to work for what I have in mind. I
believe I can't have AutoFilter on in non-contiguous regions.
I tried her technique to turn off arrows in the middle of
a region, but it didn't seem to work. Maybe I erred in
how I tried to apply the trick.

Alternatively, if I can just set up the filter in VBA for
Column Y, that would be just fine -- as long as I can also
interactively (with arrow) AutoFilter Column C later normally.

-dman-

================================================== ================
Dallman Ross wrote:

Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one
mix advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-

I believe I can't have AutoFilter on in non-contiguous regions.

I'm pretty sure you can have only 1 autofilter per sheet.
Make sure you have a heading column spanning from Column C to Column Y.
Here I assume you have a heading Column from A:Y.
Would this general idea work for you?

Sub Demo()
Dim C As Range '(C)olumn

[A1].CurrentRegion.AutoFilter

With ActiveSheet.AutoFilter.Range
'// Turn off All Arrows
For Each C In .Columns
.AutoFilter Field:=C.Column, _
VisibleDropDown:=False
Next C

'// But have Drop down arrow for Column C
.AutoFilter Field:=3, VisibleDropDown:=True

'// Now Add Filters...
.AutoFilter Field:=25, _
Criteria1:="Test Value", _
VisibleDropDown:=False
End With
End Sub


--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Dallman Ross" <dman@localhost. wrote in message
...
In , Dave Peterson
spake thusly:

If you only want to see one arror--only apply
data|filter|autofilter to that single column. (I don't see the
point of autofiltering 12 columns and then hiding 11 arrows.)

I want to see one arrow in Column C, but I want to filter a
second column way over in Column Y, but only statically; not
interactively. Column C gets the interactive filter. So it's the
only arrow I want to see. It wouldn't be tragic if there was an
arrow on Column Y too, though.

If you want to see multiple arrows, but not in contiguous
columns, you can use the technique at Debra Dalgleish's site:

http://contextures.com/xlautofilter03.html#Hide

I saw that earlier (before asking here), and tried it; but
it seemed to me not to work for what I have in mind. I
believe I can't have AutoFilter on in non-contiguous regions.
I tried her technique to turn off arrows in the middle of
a region, but it didn't seem to work. Maybe I erred in
how I tried to apply the trick.

Alternatively, if I can just set up the filter in VBA for
Column Y, that would be just fine -- as long as I can also
interactively (with arrow) AutoFilter Column C later normally.

-dman-

================================================== ================
Dallman Ross wrote:

Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one
mix advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-

If you apply the filter to just column C and filter to show/hide something based
on that column, then entire rows are hidden/shown based on that filter in C.

If you really wanted to make choices for other columns, then apply the filter to
the whole range (all multiple columns) and use the suggested code to hide the
arrows you want hidden.

Both Dana's code and that link to Debra's code will work.

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

If you only want to see one arror--only apply
data|filter|autofilter to that single column. (I don't see the
point of autofiltering 12 columns and then hiding 11 arrows.)

I want to see one arrow in Column C, but I want to filter a
second column way over in Column Y, but only statically; not
interactively. Column C gets the interactive filter. So it's the
only arrow I want to see. It wouldn't be tragic if there was an
arrow on Column Y too, though.

If you want to see multiple arrows, but not in contiguous
columns, you can use the technique at Debra Dalgleish's site:

http://contextures.com/xlautofilter03.html#Hide

I saw that earlier (before asking here), and tried it; but
it seemed to me not to work for what I have in mind. I
believe I can't have AutoFilter on in non-contiguous regions.
I tried her technique to turn off arrows in the middle of
a region, but it didn't seem to work. Maybe I erred in
how I tried to apply the trick.

Alternatively, if I can just set up the filter in VBA for
Column Y, that would be just fine -- as long as I can also
interactively (with arrow) AutoFilter Column C later normally.

-dman-

================================================== ================
Dallman Ross wrote:

Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one
mix advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-


--

Dave Peterson

In , Dana DeLouis
spake thusly:

I believe I can't have AutoFilter on in non-contiguous regions.

I'm pretty sure you can have only 1 autofilter per sheet.
Make sure you have a heading column spanning from Column C to
Column Y. Here I assume you have a heading Column from A:Y.
Would this general idea work for you?

Yes! I did get it to work. Thank you very much, Dana!
I have been driving myself crazy trying things.

That is great. I have a couple of questions, though.
First, I tried to figure out how to make it start with Column
C instead of Column A, but I didn't yet succeed. I can
live with it the way it is, but would like to know how.

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.

The other macro does start with Column A and goes all the way to
the end -- right now, that's Column AD (30 columns). But I really
only need the AutoFilter in the range of Column C:Y, as I said.
In any case, it seems non-optimal to loop an extra time through all
the columns. How would I incorporate your algorithm into what I
have already? It is as follows:

Option Explicit
Sub HideCols()

Dim iCol As Long
Dim iLastCol As Long

With Application.ActiveSheet
iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For iCol = 1 To iLastCol
If .Cells(2, iCol).Interior.ColorIndex = 24 Then
.Columns(iCol).Hidden = True
Else
.Columns(iCol).Hidden = False
End If
'Debug.Print iCol
Next iCol
End With
End Sub

(Mainly that is the nice work-product of Dave Peterson.)

Thanks,
Dallman

================================================== ==============
Sub Demo()
Dim C As Range '(C)olumn

[A1].CurrentRegion.AutoFilter

With ActiveSheet.AutoFilter.Range
'// Turn off All Arrows
For Each C In .Columns
.AutoFilter Field:=C.Column, _
VisibleDropDown:=False
Next C

'// But have Drop down arrow for Column C
.AutoFilter Field:=3, VisibleDropDown:=True

'// Now Add Filters...
.AutoFilter Field:=25, _
Criteria1:="Test Value", _
VisibleDropDown:=False
End With
End Sub

In , Dave Peterson
spake thusly:

If you apply the filter to just column C and filter to show/hide
something based on that column, then entire rows are hidden/shown
based on that filter in C.

Yup. Understood.

If you really wanted to make choices for other columns, then
apply the filter to the whole range (all multiple columns) and
use the suggested code to hide the arrows you want hidden.

Both Dana's code and that link to Debra's code will work.

Thanks, Dave. I don't know why I couldn't get Debra's
code to work when I tried it two weeks ago, but I am
sure the error was at my end, not with her code. Even
though I'm still a "VBA infant," I am further along than
I was a couple of weeks ago, so I'll go look again and see
what I can learn from her stuff now. Much appreciated!

-dman-

==============================================
Dallman Ross wrote:

In , Dave Peterson
spake thusly:

If you only want to see one arror--only apply
data|filter|autofilter to that single column. (I don't see the
point of autofiltering 12 columns and then hiding 11 arrows.)

I want to see one arrow in Column C, but I want to filter a
second column way over in Column Y, but only statically; not
interactively. Column C gets the interactive filter. So it's the
only arrow I want to see. It wouldn't be tragic if there was an
arrow on Column Y too, though.

If you want to see multiple arrows, but not in contiguous
columns, you can use the technique at Debra Dalgleish's site:

http://contextures.com/xlautofilter03.html#Hide

I saw that earlier (before asking here), and tried it; but
it seemed to me not to work for what I have in mind. I
believe I can't have AutoFilter on in non-contiguous regions.
I tried her technique to turn off arrows in the middle of
a region, but it didn't seem to work. Maybe I erred in
how I tried to apply the trick.

Alternatively, if I can just set up the filter in VBA for
Column Y, that would be just fine -- as long as I can also
interactively (with arrow) AutoFilter Column C later normally.

-dman-

================================================== ================
Dallman Ross wrote:

Can auto-filter be engaged for a column, yet not have the
pull-down arrow showing? I'd prefer that. Or can one
mix advanced filtering and auto-filter?

I'm using auto-filtering across about 12 columns, but really
only need it actively on one; the others, I have it activated
in because I set something once and leave it alone thereafter.
I'd prefer not to have to stare at all the arrows.

-dman-

Hi. You can select the Headings that will have the drop-down arrows
directly.
Then you can continue with the code. Here's a simple demo you can test with
a blank sheet.

Sub TestIt()
With [A1:AD10]
.Value = "Junk"
.Rows(1) = "Heading"
End With

Range("C1:Y1").AutoFilter
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Dallman Ross" <dman@localhost. wrote in message
...
In , Dana DeLouis
spake thusly:

I believe I can't have AutoFilter on in non-contiguous regions.

I'm pretty sure you can have only 1 autofilter per sheet.
Make sure you have a heading column spanning from Column C to
Column Y. Here I assume you have a heading Column from A:Y.
Would this general idea work for you?

Yes! I did get it to work. Thank you very much, Dana!
I have been driving myself crazy trying things.

That is great. I have a couple of questions, though.
First, I tried to figure out how to make it start with Column
C instead of Column A, but I didn't yet succeed. I can
live with it the way it is, but would like to know how.

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.

The other macro does start with Column A and goes all the way to
the end -- right now, that's Column AD (30 columns). But I really
only need the AutoFilter in the range of Column C:Y, as I said.
In any case, it seems non-optimal to loop an extra time through all
the columns. How would I incorporate your algorithm into what I
have already? It is as follows:

Option Explicit
Sub HideCols()

Dim iCol As Long
Dim iLastCol As Long

With Application.ActiveSheet
iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For iCol = 1 To iLastCol
If .Cells(2, iCol).Interior.ColorIndex = 24 Then
.Columns(iCol).Hidden = True
Else
.Columns(iCol).Hidden = False
End If
'Debug.Print iCol
Next iCol
End With
End Sub

(Mainly that is the nice work-product of Dave Peterson.)

Thanks,
Dallman

================================================== ==============
Sub Demo()
Dim C As Range '(C)olumn

[A1].CurrentRegion.AutoFilter

With ActiveSheet.AutoFilter.Range
'// Turn off All Arrows
For Each C In .Columns
.AutoFilter Field:=C.Column, _
VisibleDropDown:=False
Next C

'// But have Drop down arrow for Column C
.AutoFilter Field:=3, VisibleDropDown:=True

'// Now Add Filters...
.AutoFilter Field:=25, _
Criteria1:="Test Value", _
VisibleDropDown:=False
End With
End Sub

In , Dana DeLouis
spake thusly:

Hi. You can select the Headings that will have the drop-down arrows
directly.
Then you can continue with the code. Here's a simple demo you
can test with a blank sheet.

Sub TestIt()
With [A1:AD10]
.Value = "Junk"
.Rows(1) = "Heading"
End With

Range("C1:Y1").AutoFilter
End Sub


Okay, that's helpful. Tomorrow I'll try to see if I can figure
out how to limit the other actions to those columns as well.
Maybe I'll even figure out getting the hide-arrows macro combined
with my other macro, who knows. Thanks again!

Dallman

In , 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
---------------------------------------------------------------

Problem 1

Hi. Check the "AutoFilterMode" Property.
Here's one idea...

Sub Demo()
'// Near Beginning of Code
With ActiveSheet
'// Remove AutoFilter if On
If .AutoFilterMode Then .AutoFilterMode = False
End With

'// Now, start w/ your code
'// ...etc

End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Dallman Ross" <dman@localhost. wrote in message
...
In , 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
---------------------------------------------------------------

In , Dana DeLouis
spake thusly:

Ah. cool beans. Thanks, Dana.

Now on to problem 2 ... :-)

=dman=

=======================================
Problem 1

Hi. Check the "AutoFilterMode" Property.
Here's one idea...

Sub Demo()
'// Near Beginning of Code
With ActiveSheet
'// Remove AutoFilter if On
If .AutoFilterMode Then .AutoFilterMode = False
End With

'// Now, start w/ your code
'// ...etc

End Sub

Now on to problem 2 ... :-)

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.)


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

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

For iCol = 1 To iLastCol

I am guessing that the above line should read
For iCol = 3 To 25

Does this help??

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Dallman Ross" <dman@localhost. wrote in message
...
In , Dana DeLouis
spake thusly:

Ah. cool beans. Thanks, Dana.

Now on to problem 2 ... :-)

=dman=

=======================================
Problem 1

Hi. Check the "AutoFilterMode" Property.
Here's one idea...

Sub Demo()
'// Near Beginning of Code
With ActiveSheet
'// Remove AutoFilter if On
If .AutoFilterMode Then .AutoFilterMode = False
End With

'// Now, start w/ your code
'// ...etc

End Sub

In , Dana DeLouis
spake thusly:

Now on to problem 2 ... :-)

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


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

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

For iCol = 1 To iLastCol

I am guessing that the above line should read For iCol = 3 To 25

Does this help??

In , Dana DeLouis
spake thusly:

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