Show Filter Drop Down Arrows

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
1,526
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
 
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.
@Alex Blakenburg Tried all permutations & Combinations before troubling you.

Here is the complete modified code.
Please advise.
Thanks a lot 🙏

VBA Code:
Sub BanksJumpEoM()
'
' BanksJumpEoM Macro
'

'
    'To shift sheets for Call GoHome to work properly
    Sheets("Client").Select
    
    'BanksJump starts here
    Sheets("Bank").Select
    
    Call GoHome
        
    Application.ScreenUpdating = True
    
    Range("Banks[[#Headers],[Dt]]").Select
    
    'To clear filter from Table Bank
    'ActiveSheet.AutoFilterMode = False
    
    'To Turn off AutoFilter before showing Filter Arrow
    Dim tblBanks As ListObject
    Set tblBanks = Range("Banks").ListObject
        
    With tblBanks
        'To 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
    
    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.AutoFilterMode = True
    ActiveSheet.ListObjects("Banks").ShowAutoFilterDropDown = True
    Selection.AutoFilter
    
End Sub
 
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