Auto-filter question

[Dallman Ross]

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-

 

[Alan]

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.

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

 

[Dallman Ross]

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-

 

[Dallman Ross]

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-

 

[Dallman Ross]

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]

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-

 

[Dave Peterson]

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]

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-

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

 

[Dana DeLouis]

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

 

[Dave Peterson]

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]

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

 

[Dallman Ross]

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-

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

 

[Dana DeLouis]

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

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

 

[Dallman Ross]

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

 

[Dallman Ross]

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

 

[Dana DeLouis]

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

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

 

[Dallman Ross]

In <#[email protected]>, Dana DeLouis
<[email protected]> spake thusly:

Ah. cool beans. Thanks, Dana.

Now on to problem 2 ...

=dman=

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

 

[Dana DeLouis]

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