Apply Autofilter and Hide Arrows

stretchr

New Member
Joined
Aug 27, 2009
Messages
8
Hi all,

I have a macro which successfully runs activated by a button, to apply an autofilter. This is the code:

Code:
Sub ReduceOPCList()'
' ReduceOPCList Macro
' Reduce OPC List
'


'
    ActiveSheet.AutoFilterMode = False
    
    With ActiveSheet.Range("B10:I72")
    .AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlOr, Criteria2:="=***"
    .AutoFilter Field:=8, Criteria1:="<>0", Operator:=xlOr, Criteria2:="=***"
    End With
    
End Sub

Now I also want to hide the Autofilter arrows in row 10.

I have this code, which when I run, hides the arrows but also reverses the effect of the Autofilter (ie all my hidden rows appear again)

Code:
Sub HideArrows()'
' HideArrows Macro
' Hide all AutoFilter Arrows in Range


Dim c As Range
Dim i As Integer
Dim rng As Range
Set rng = Range("B10:I10")
i = rng.Cells(1, 1).Column - 1
Application.ScreenUpdating = False
For Each c In Range("B10:I10")
    c.AutoFilter Field:=c.Column - i, _
      Visibledropdown:=False
Next
Application.ScreenUpdating = True
End Sub

How do I get around this and hopefully hide the arrows in the same step as applying the autofilters?

Thanks in advance!
Richard
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Suggestion1
Use Advanced Filter, filtering the list in-place
- filter arrows are hidden by default
https://www.contextures.com/xladvfilter01.html

Suggestion 2
Copy Paste the filtered data including row 10 to another sheet

Suggestion 3
Add row 9 with same values as headers in row10
After filtering Hide row 10
 
Last edited:
Upvote 0
Thanks for your suggestions Yongle,

From what I see, advanced filtering won't work as it requires no blank rows in the data (I have a number of them)

This project is to set up a template which can be re-used with ease by non-technical staff. So copy pasting the filtered data won't work, especially as that would have to be done a number of times through the life of each time the spreadsheet is used - it's a living document and the empty/non-empty rows are liable to change state numerous times (it's for an opinion of costs).

Suggestion 3 could potentially work, but it removes the simplicity I was hoping for (anyone not familiar with the setup would likely open the document, see the apparently duplicate row, and delete one of them).

Thanks for trying!
 
Upvote 0
Ok so I found I could add visibledropdown as an attribute, so now I have this:

Code:
    With ActiveSheet.Range("B10:I72")    .AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlOr, Criteria2:="=***", Visibledropdown:=False
    .AutoFilter Field:=8, Criteria1:="<>0", Operator:=xlOr, Criteria2:="=***", Visibledropdown:=False
    .AutoFilter Field:=2, Visibledropdown:=False
    .AutoFilter Field:=3, Visibledropdown:=False
    .AutoFilter Field:=4, Visibledropdown:=False
    .AutoFilter Field:=5, Visibledropdown:=False
    .AutoFilter Field:=6, Visibledropdown:=False
    .AutoFilter Field:=7, Visibledropdown:=False
    End With

which works fine, but it's got me thinking there must be a way to set Visibledropdown once for the whole range? I mean if I had 30-80 columns or more to apply it to, that would be a lot of lines!
 
Upvote 0
From what I see, advanced filtering won't work as it requires no blank rows in the data (I have a number of them)

Works for me, provided the whole of the data is included in criteria range
- set the full range before filtering in VBA

Suggestion 3 could potentially work, but it removes the simplicity I was hoping for (anyone not familiar with the setup would likely open the document, see the apparently duplicate row, and delete one of them).

Use VBA to ensure only one of the rows is visible by unhiding the one when the other is hidden
 
Upvote 0
which works fine, but it's got me thinking there must be a way to set Visibledropdown once for the whole range? I mean if I had 30-80 columns or more to apply it to, that would be a lot of lines!

use a loop ...
Code:
        For f = 1 To 8
            If f <> 1 And f <> 8 Then
            .AutoFilter Field:=f, Visibledropdown:=False
        Next
 
Last edited:
Upvote 0
@stretchr, you don't actually need an End If, because you are only doing one test you can put it all on a single line i.e.

Code:
        For f = 1 To 8
            If f <> 1 And f <> 8 Then .AutoFilter Field:=f, Visibledropdown:=False
        Next
 
Upvote 0
Well, I tried it without the End If, and it complained about the Next not having a For. But with the End If included, it worked ... that's all I know!
 
Upvote 0
That's because it was on 2 lines which you don't have to do for a single If test. See the code I posted and test.
 
Upvote 0

Forum statistics

Threads
1,203,488
Messages
6,055,716
Members
444,811
Latest member
NotJack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top