Apply Autofilter and Hide Arrows

[stretchr]

Hi all,

I have a macro which successfully runs activated by a button, to apply an autofilter. This is the 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)

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

 

[Yongle]

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

 

[stretchr]

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!

 

[stretchr]

Ok so I found I could add visibledropdown as an attribute, so now I have this:

    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!

 

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

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

 

[Yongle]

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

        For f = 1 To 8
            If f <> 1 And f <> 8 Then
            .AutoFilter Field:=f, Visibledropdown:=False
        Next

 

[stretchr]

Thanks, the loop works fine, except you forgot the End If

 

[MARK858]

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

        For f = 1 To 8
            If f <> 1 And f <> 8 Then .AutoFilter Field:=f, Visibledropdown:=False
        Next

 

[stretchr]

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!

 

[MARK858]

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.