Autofilter popup

raskinsm · Dec 5th 2019

I have a need. The same filtering methods are done monthly on roughly 15k rows of data. Nothing changes, filtering-wise, other than the date. The date filter is complex so-to-speak, It is broken up by Year/Month/Day/Time.

What I would like is that when the macro runs and it comes to field:=12 (Column L) a popup occurs which contains the exact same options as what is present when done manually. I will include code below. However, if this becomes moreso an act of god to achieve, I could be happy if the MACRO selected all entries within the year/month I am within when running the MACRO. I have many MACROS that require this ability, however, I will include a simple one.

Code

Sheets("Data Drop").Activate
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
ActiveSheet.Range("A1").AutoFilter Field:=3
ActiveSheet.Range("$A$1:$AC$200000").AutoFilter Field:=10
ActiveSheet.Range("$A$1:$AC$200000").AutoFilter Field:=10, Criteria1:="Yes"
ActiveSheet.Range("$A$1:$AC$149961").AutoFilter Field:=12, Criteria1:=7, Operator:=11, Criteria2:=0, SubField:=0
ActiveSheet.Range("$A$1:$AC$200000").AutoFilter Field:=21
ActiveSheet.Range("$A$1:$AC$200000").AutoFilter Field:=21, Criteria1:="Development"
ActiveSheet.Range("$A$1:$AC$200000").AutoFilter Field:=25
ActiveSheet.Range("$A$1:$AC$200000").AutoFilter Field:=25, Criteria1:="Signatures completed"
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
MsgBox rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 & " of " & rng.Rows.Count - 1 & " Records"
Sheets("Control Panel").Select

Display More

Here is the screen shot of the filter, in this example, I'd want everything selected in December:

**royUK** · Dec 6th 2019

I've tidied up your existing code.

Code

Dim rng As Range
    With Sheets("Data Drop")
        If .FilterMode Then .ShowAllData
        Set rng = .Range("A1").CurrentRegion

        With rng
            .AutoFilter Field:=10, Criteria1:="Yes"
            .AutoFilter Field:=12, Criteria1:=7, Operator:=11, Criteria2:=0, SubField:=0
            .AutoFilter Field:=21, Criteria1:="Development"
            .AutoFilter Field:=25, Criteria1:="Signatures completed"
        End With

        Set rng = .AutoFilter.Range
        ''// use on error because SpecialCells will error if no cells to match the criteria are found
        On Error Resume Next
        MsgBox rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 & " of " & rng.Rows.Count - 1 & " Records"
        On Error GoTo 0
    End With

Display More

Can you attach a small example of your file.

raskinsm · Dec 10th 2019

Thank you, I followed suit with other snippets I have and it is much cleaner and works fine.

Autofilter popup

Participate now!

Share

Tags