How to Remove/Hide Some Filter Arrows from Table Headers

[Excel Explore]

Hello you Wonderful People,

Can somebody please tell me (the VBA) how I could remove/hide some of the filter/sort arrows in the header of an excel table while keeping others - so users would still be able to sort the whole data range within the table using only the visible arrows?
My table headers are from C9:AA9
Data to be entered in C10:AA409
I'd like to hide the arrows in F9, H9, J9, L9, N9, P9, R9, S9, T9, V9, AA9.

I'm using Excel 2010 on Windows 8.1.

Thanks.

Excel Explore.

 

[njimack]

Take a look at http://www.contextures.com/xlautofilter03.html#Hide

 

[Excel Explore]

Neil,

Thanks for that link. It has a host of VBA tricks - but Im afraid my problem remains.

I Copy-pasted this first code:

Sub HideSpecifiedArrows()
'hides arrows in specified columns
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
Select Case c.Column
Case 4, 6, 8, 10, 12, 14, 16, 17, 18, 20, 25
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
Case Else
c.AutoFilter Field:=c.Column, _
Visibledropdown:=True
End Select
Next
Application.ScreenUpdating = True
End Sub

..... But it didn't work.

So I copy-pasted the next code:

Sub HideArrowsRange()
'hides arrows in specified range
Dim c As Range
Dim i As Integer
Dim rng As Range
Set rng = Range("C9:AA9")
i = rng.Cells(1, 1).Column - 1
Application.ScreenUpdating = False
For Each c In Range("C9:AA9")
Select Case c.Address
Case "$F$9", "$H$9", "$J$9", "$L$9", "$N$9", "$P$9", "$R$9", "$S$9", "$T$9", "$V$9", "$AA$9"
c.AutoFilter Field:=c.Column - i, _
Visibledropdown:=False
Case Else
c.AutoFilter Field:=c.Column - i, _
Visibledropdown:=True
End Select
Next
Application.ScreenUpdating = True
End Sub

.... But that also failed to work.

It might be that I'm not pasting the code in the right place (I put them under "General" in the VBA section).

Any suggestions are most welcomed & appreciated.

Excel Explore

 

[njimack]

The code on that site only hides the arrows in specific columns, you need to amend it to suit your needs.

If you want to hide all arrows, just take out the Select Case part of the code.

 

[Excel Explore]

Yes, I'm trying to hide arrows on specific columns - not all columns.
Those numbers in the "select case" part & absolute cell references in the above codes are adaptations to my worksheet's.... But it doesn't hide the arrows.

 

[R Pelle]

I played around with your 1st example (Excel 2010)

[TABLE="width: 395"]
<tbody>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[/TR]
[TR]
[TD]Name
[/TD]
[TD]Address
[/TD]
[TD]Postal Code
[/TD]
[TD]City
[/TD]
[TD]telnr
[/TD]
[TD]Province
[/TD]
[/TR]
[TR]
[TD]Name 1
[/TD]
[TD]Street 1
[/TD]
[TD="align: right"]1234
[/TD]
[TD]ll
[/TD]
[TD]1234
[/TD]
[TD]ffffffff
[/TD]
[/TR]
[TR]
[TD]Name 2
[/TD]
[TD]Street 2
[/TD]
[TD="align: right"]6543
[/TD]
[TD]kk
[/TD]
[TD]4422
[/TD]
[TD]ggggggggg
[/TD]
[/TR]
[TR]
[TD]Name 3
[/TD]
[TD]Street 3
[/TD]
[TD="align: right"]9875
[/TD]
[TD]mm
[/TD]
[TD]5678
[/TD]
[TD]bbbbbbbb
[/TD]
[/TR]
</tbody>[/TABLE]




Sub HideA1F1()
 Dim c As Range
 Dim i As Integer
 i = Cells(1, 1).End(xlToRight).Column
 
 'Name, Address, PostCode, City, Telnr, Province
 For Each c In Range(Cells(1, 1), Cells(1, i))
    Select Case c.Column
    Case 3, 5 ' postcode & telnr
        c.AutoFilter Field:=c.Column, _
        Visibledropdown:=False
    Case Else
        c.AutoFilter Field:=c.Column, _
        Visibledropdown:=True
    End Select
 Next
 
 End Sub

This hides the arrows for Postalcode & TelNr


Then I inserted 2 blank columns so that the data-table starts in Column C

Sub HideC1H1()
 Dim c As Range
 Dim i As Integer
 i = Cells(1, 3).End(xlToRight).Column
 i = i - 2
 
 'Blank, blank, Name, Address ,PostCode, City, Telnr, Province
 For Each c In Range(Cells(1, 3), Cells(1, i))
     Select Case c.Column
        Case 3, 5 'Postcode & Telnr
            c.AutoFilter Field:=c.Column, _
             Visibledropdown:=False
         Case Else
            c.AutoFilter Field:=c.Column, _
            Visibledropdown:=True
     End Select
 Next
 
 End Sub

And it works too.

Finally I inserted 8 blank rows sow the column headings start in row 9 (as per your example)

Sub HideC9H9()
 'hides arrows in specified columns
 Dim c As Range
 Dim i As Integer
 i = Cells(9, 3).End(xlToRight).Column
 i = i - 2
 
 'Row 9: Blank, blank, Name, Address,PostCode,City, Telnr,Province
 For Each c In Range(Cells(9, 3), Cells(9, i))
     Select Case c.Column
        Case 3, 5 'Postcode & Telnr
            c.AutoFilter Field:=c.Column, _
             Visibledropdown:=False
         Case Else
            c.AutoFilter Field:=c.Column, _
            Visibledropdown:=True
     End Select
 Next
 
 End Sub

Works too!
Give it a try

 

[Excel Explore]

Rob,

These arrows are just stubborn!! But I thank you for your time on this. I tried the first of your three codes - for hiding arrows - but after I save and even close and reopen the worksheet, the arrows Do Not Hide.

Its an excel table: with the table headers in C9, D9, E9, F9, etc, until AA9.

I just need to hide the filter/sort arrows in more than three of these table headers in random order.

I'm into SOS mode now.

Thanks in advance for any suggestions.

Excel Explore

 

[Zack Barresse]

This is the approach I would take:

Sub ShowSpecificFilterArrows()

    Dim Table As ListObject
    Dim TableColumn As ListColumn
    
    Const VisibleColumns As String = "|6|8|10|12|14|16|17|18|20|25|"
    Const VisibleColumns2 As String = "|H|J|L|N|P|R|S|T|V|AA|"
    
    Set Table = ActiveSheet.ListObjects(1)
    For Each TableColumn In Table.ListColumns
        
        'Using column index of table - VisibleColumns
        TableColumn.Range.AutoFilter TableColumn.Index, , , , CBool(InStr(1, VisibleColumns, "|" & TableColumn.Index & "|", vbTextCompare) <> 0)
        
        'Using column letter of worksheet - VisibleColumns2
        TableColumn.Range.AutoFilter TableColumn.Index, , , , CBool(InStr(1, VisibleColumns2, "|" & Split(TableColumn.Range(1, 1).Address(1, 0), "$")(0) & "|", vbTextCompare) <> 0)
        
    Next TableColumn

End Sub

There are two options there, both are utilizing constants. The first constant (VisibleColumns) is the column numbers in relation to the Table. So 1 would be the first column of the Table. Since your Table starts in column C, column H in the Table is the 6th column, instead of column 8 of the Sheet.

The other option (VisibleColumns2) is for using the letter of the Sheet column.

Either one will work exactly as the other. The important thing to know is this will clear any filter currently applied. We could add some code to trap what is currently filtered in those columns, and then re-apply it afterwards, but that would make the code quite a bit larger.

Let us know if this is what you're looking for.

HTH

 

[Excel Explore]

Hello Mr Barres
I appreciate your time and suggestion. Thanks.

I tried your code as follows, but I've been beaten yet again:

Sub ShowSpecificFilterArrows()
Dim Table As ListObject
Dim TableColumn As ListColumn
Const VisibleColumns As String = "|4|6|8|10|12|14|16|17|18|20|25|"
Const VisibleColumns2 As String = "|F|H|I|K|M|O|Q|S|T|V|AA|"
Set Table = ActiveSheet.ListObjects(1)
For Each TableColumn In Table.ListColumns
'Using column index of table - VisibleColumns
TableColumn.Range.AutoFilter TableColumn.Index, , , , CBool(InStr(1, VisibleColumns, "|" & TableColumn.Index & "|", vbTextCompare) <> 0)
'Using column letter of worksheet - VisibleColumns2
TableColumn.Range.AutoFilter TableColumn.Index, , , , CBool(InStr(1, VisibleColumns2, "|" & Split(TableColumn.Range(1, 1).Address(1, 0), "$")(0) & "|", vbTextCompare) <> 0)
Next TableColumn
End Sub

Basically, I'd like to hide some arrows and retain others, and still be able to sort the data in the exel table (including data in the column without arrows in the headers) from those still visible arrows.

Columns C and D have peoples names; the rest from E to AA, contain numbers. Most of the numbers are two or three digit figures in narrow columns - so you can imagine the ugly look many arrows close to each other (I've managed to hide the text headers - but arrows!)

I hope I'm not asking too much, or trying to achieve the impossible.

Thanks in advance.