Show Filter Drop Down Arrows

[SanjayGMusafir]

Hi Experts
I'm using following VBA to show Filter Drop Down Arrows

For some reason it has stopped working.

Please help

'To Hide/Show Filter Arrow
    On Error Resume Next
    Err.Clear
    
    ActiveSheet.ListObjects("Banks").ShowAutoFilterDropDown = True
    Selection.AutoFilter

 

[6StringJazzer]

Are you starting with a table that has dropdown arrows hidden, and you want to show them? Your code works fine for me. The most likely problem is that the active sheet does not have a table named Banks.

Also, what is Selection when you run this code? That line seems unrelated to the previous line.

    On Error Resume Next

This is a poor practice because it hides errors when you have not anticipated what errors could occur. It is very likely that your code is raising an error (e.g., table does not exist) but you are suppressing error notification. Before you do anything else run your code without this.

 

[SanjayGMusafir]

Are you starting with a table that has dropdown arrows hidden

@6StringJazzer Thanks for reverting back

Answers to your queries -

• My drop down arrows generally remain hidden. So I use VBA to show when I need
• Banks is the table I use all the day - And for some reason it stopped working on that table.
• Selection is the Current Date, End of Month, Beginning of Month in Banks table - Depends upon the vba code I'm running
• The error line I added today as I was unable to figure out what was causing problem.

Just FYI -
Code was working fine with me. About 2 months back I shifted from Mac to Windows.
And it was just yesterday I realized that code was not showing filter arrows, when it showed on some another table, with the same code.

I'm on experiments since. Finally came to the Forum to find some solution.

Would appreciate if you give some useful ideas to get things to work.

Thanks Again

 

[6StringJazzer]

I cannot reproduce your problem and I don't see an issue in the code, which is very simple. Normally the way I would troubleshoot this is to examine the file. If you have cloud storage where you can share a link to the file (or a copy) I'll take a look.

 

[SanjayGMusafir]

@6StringJazzer it's on my desktop. Else would have shared for your reviews.

Thanks Again

 

[6StringJazzer]

My drop down arrows generally remain hidden. So I use VBA to show when I need

If you use code to show it, you must also have code that hides it. Can you show all your code?

 

[SanjayGMusafir]

This is the full code.

Thanks a lot

To hide I use

ActiveSheet.ListObjects("Banks").ShowAutoFilterDropDown = False

Underneath is the full code where problem surfaced...

Sub BanksJumpEoM()
'
' BanksJumpEoM Macro
'

'
    'To shift sheets for Call GoHome to work properly
    Sheets("Client").Select
  
    'BanksJumpToday Starts here
    Sheets("Bank").Select
  
    Call GoHome
      
    Application.ScreenUpdating = True
  
    Range("Banks[[#Headers],[Dt]]").Select
  
    'To clear filter from Table Bank
    ActiveSheet.AutoFilterMode = False

    On Error Resume Next
    ActiveSheet.ShowAllData
    Err.Clear

    ActiveWorkbook.Worksheets("Bank").ListObjects("Banks").Sort.SortFields.Clear
  
    'To go to EoMonth in a filtered range
    Dim ar, i As Long, j As Long, Dt As Long, ws As Worksheet, LRow As Long
    Set ws = Worksheets("Bank")
    LRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
    ar = ws.Range("C6", Cells(Rows.Count, "C").End(xlUp))
    Dt = Evaluate("EOMonth(Today(), 0)+1")
  
    For i = 1 To UBound(ar, 1)
        If ar(i, 1) = CLng(Dt) And Cells(i + 5, 3).EntireRow.Hidden = False Then
            Cells(i + 5, 3).Select
            Exit Sub
        ElseIf ar(i, 1) > CLng(Dt) Then
            For j = 1 To (LRow - 1)
                If Cells(i - j + 5, 3).EntireRow.Hidden = False Then
                    Cells(i - j + 5, 3).Select
                    Exit Sub
                End If
            Next j
        End If
    Next i
  
    'To Hide/Show Filter Arrow
    ActiveSheet.ListObjects("Banks").ShowAutoFilterDropDown = True
    Selection.AutoFilter
      
End Sub

 

[Alex Blakenburg]

You really want to avoid using Select (and Activate) in your code.
At the moment some of your code is working directly with the table and some is working at the worksheet level.
Your ShowAutoFilterDropDown will fail if Autofilter is turned off.

See if this helps.

    Dim tblBanks As ListObject
    Set tblBanks = Range("Banks").ListObject
   
    With tblBanks
        ' Make sure AutoFilter is turned on before proceeding
        If .ShowAutoFilter = False Then
            .Range.AutoFilter
        End If
        'To clear filter from Table Bank
        .AutoFilter.ShowAllData
        .ShowAutoFilterDropDown = False
   
        .Sort.SortFields.Clear   
    End With

 

[SanjayGMusafir]

@Alex Blakenburg , @6StringJazzer
Thanks for helping

I believe problem could be somewhere else that I'm not able to think of.

None of the above solutions worked my me.

Thanks Again

 

[Alex Blakenburg]

If you want to show us the code you used after implementing the changes I suggested and show us the error message and what line was highlighted when you clicked on debug, I am happy to take a look at it.