Show Filter Drop Down Arrows

SanjayGMusafir

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
1,532
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
 
Sort by date Sort by votes
Alex Blakenburg said:
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.
Click to expand...
@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
There are some duplications in the code but it works for me.
What is your current issue ? If it is creating an error what is the exact error message and which line of the code is highlighted ?

In terms of duplication, you can delete these lines they are handled in the lines before that:
Rich (BB code): 
    On Error Resume Next
    ActiveSheet.ShowAllData
    Err.Clear

    ActiveWorkbook.Worksheets("Bank").ListObjects("Banks").Sort.SortFields.Clear

In these lines the first line turns "on" the drop down buttons but the 2nd line removes the autofilter.
No point in removing the buttons if you are going to remove the filter.
Do one or the other.
Rich (BB code): 
    ActiveSheet.ListObjects("Banks").ShowAutoFilterDropDown = True
    Selection.AutoFilter

If you are going to keep the 2nd line removing the autofilter then use:
Rich (BB code): 
tblBanks.Range.AutoFilter
 
Upvote 0
Alex Blakenburg said:
If it is creating an error what is the exact error message and which line of the code is highlighted ?
Click to expand...
@Alex Blakenburg Thanks for reverting

I have to use clear error as at times my table has no filters applied and thus it generates error.

As of,
When I run the entire code as it is
  • No error is generated
  • But, It doesn't show drop down filter arrows
After altering the code as you advised - The problem persists

Strangely it worked well on mac but problem triggered after switching to windows.

Hope I'm able to explain the situation.

Please advise

Thanks a lot 🙏
 
Upvote 0
SanjayGMusafir said:
But, It doesn't show drop down filter arrows
Click to expand...
It doesn't show the filter arrows because the next line turns off the autofilter.

Try running it just the way I have it here below:
(I have only changed code relating to the filters)

Rich (BB code): 
Sub BanksJumpEoM_post14()
'
' 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 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
    
    '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
    tblBanks.ShowAutoFilterDropDown = True
    
End Sub
 
Upvote 0
@Alex Blakenburg

There is one code where it works every time. I have tried to find the difference but couldn't

I'm giving it here, so that you may be able to help me in a better way.

Thanks a lot 🙏

It works every time👇 -

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

'
    Sheets("Bank").Select
    
    'To reset Zoom levels of the sheet
    Call ZoomReset
    
    ActiveSheet.AutoFilterMode = False

    On Error Resume Next
     ActiveSheet.ShowAllData
    Err.Clear

    ActiveWorkbook.Worksheets("Bank").ListObjects("MF").Sort.SortFields.Clear
    
    ActiveWorkbook.Worksheets("Bank").ListObjects("MF").Sort.SortFields.Add2 Key _
        :=Range("MF[Type]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Bank").ListObjects("MF").Sort.SortFields.Add2 Key _
        :=Range("MF[Fund]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Bank").ListObjects("MF").Sort.SortFields.Add2 Key _
        :=Range("MF[Plan]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Bank").ListObjects("MF").Sort.SortFields.Add2 Key _
        :=Range("MF[Name]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Bank").ListObjects("MF").Sort.SortFields.Add2 Key:= _
        Range("MF[Portfolio]"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Bank").ListObjects("MF").Sort.SortFields.Add2 Key _
        :=Range("MF[Allotted]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Bank").ListObjects("MF").Sort.SortFields.Add2 Key _
        :=Range("MF[Dt]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Bank").ListObjects("MF").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    'To go to the Top
    Range("MF[[#Headers],[Type]]").Select
    ActiveCell.Offset(1, 0).Select
    
    'To Hide/Show Filter Arrow
    ActiveSheet.AutoFilterMode = True
    ActiveSheet.ListObjects("MF").ShowAutoFilterDropDown = True
    'Selection.AutoFilter
    
    'ActiveWorkbook.Save
    
End Sub
 
Upvote 0
You need to get out of the habit of just saying "It didn't work"
What didn't work ?
Did it error out ?
What line was highlighted if it did ?
Maybe step through the With ... End with of the filter section to see what each does
Put a breakpoint on the last AutoFilterDropDown = True and see the before and after executing it.
 
Upvote 0
Alex Blakenburg said:
You need to get out of the habit of just saying "It didn't work"
What didn't work ?
Did it error out ?
What line was highlighted if it did ?
Maybe step through the With ... End with of the filter section to see what each does
Put a breakpoint on the last AutoFilterDropDown = True and see the before and after executing it.
Click to expand...
Apologies @Alex Blakenburg

The MF code shows filter drop down every time
While the Code you gave I simply copy-pasted and ran but it didn't show filter drop down arrows after running.
Please advice
Apologies again and Thanks a lot 🙏
 
Upvote 0
I am not sure what you think the MF code is doing because it never turns off the drop down buttons so the turning it back on doesn't do anything, in fact it errors out.
Its only the On Error resume next that stops the code from aborting.

For that matter why are you turning the arrows off anyway ? If the code simply turns it back on at the end the user will never see it with the buttons turned off.
Also do you have 2 tables on the Bank sheet ie both Banks and MF ?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
1K
zero269
Z
R
  • Solved
VBA to Navigate Drop Down List and Save page as PDF
Replies
9
Views
260
Ramadan
R
K
  • Question Question
Two Column VBA Drop Down only to show rows for non-blank cells.
Replies
0
Views
74
keromero
K
C
  • Solved
Drop-down menus in tables and removing used items from that menu
2
Replies
11
Views
553
Peter_SSs
Peter_SSs
R
  • Question Question
Copy html table with dropdown menu into excel vba
Replies
5
Views
101
Reis
R

Share this page

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