Show Filter Drop Down Arrows

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
1,523
Office Version
  1. 2024
Platform
  1. Windows
Hi Experts
I'm using following VBA to show Filter Drop Down Arrows

For some reason it has stopped working.

Please help

VBA Code:
'To Hide/Show Filter Arrow
    On Error Resume Next
    Err.Clear
    
    ActiveSheet.ListObjects("Banks").ShowAutoFilterDropDown = True
    Selection.AutoFilter
 
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.
VBA Code:
    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.
 
Upvote 0
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 🙏
 
Upvote 0
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.
 
Upvote 0
This is the full code.

Thanks a lot 🙏

To hide I use

VBA Code:
ActiveSheet.ListObjects("Banks").ShowAutoFilterDropDown = False

Underneath is the full code where problem surfaced...

VBA Code:
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
 
Upvote 0
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.

VBA Code:
    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
 
Last edited:
Upvote 0
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.
 
Upvote 0

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