Excel Macro to capture selected filter criteria

Question

please can you help to fetch what criteria user gave to filter the data in excel and then use that in macro to append extra filter condition.

i have done below till now. how to build the dynamic criteria, me like if there are multiple conditions, do we have any function to capture the criteria and then append ?

    With Rng.Parent.AutoFilter

    If Intersect(Rng, .Range) Is Nothing Then GoTo Finish

    With .Filters(Rng.Column - .Range.Column + 1)
        If Not .On Then GoTo Finish
        Filter = .Criteria1
        Filter = Filter & " OR " & Cells(4, 10)
        Select Case .Operator
            Case xlAnd
                Filter = Filter & " AND " & .Criteria2
            Case xlOr
                Filter = Filter & " OR " & .Criteria2
        End Select
    End With

https://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save-restore-a-user-defined-filter — Tim Williams, Aug 11 '17 at 05:56

score 3 · Answer 1 · answered Aug 11 '17 at 04:17

Here is some simple code to get you started using .AutoFilter with VBA on some sample data. Notice that multiple criteria are used to filter the data as you requested, and that you can dynamically change the filter criteria if you like. The animated gif shows stepping through the code. The r.select statement is just to help you understand, and should be removed once you understand. After filtering the data some of the filtered data is selected and copied to another sheet. Of course, you would modify as needed.

Option Explicit
Sub test()
Dim sh1 As Worksheet, sh2 As Worksheet, r As Range, filteredRange As Range
Dim chosenName As String
Const idCol = 1, nameCol = 2, cityCol = 4, chosenCity = "Denver"

chosenName = InputBox("What name to filter by?")
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set r = sh1.Range("A1")
sh1.Activate
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

    r.AutoFilter field:=nameCol, Criteria1:="=" & chosenName
    r.AutoFilter field:=cityCol, Criteria1:="=" & chosenCity
    Set r = sh1.AutoFilter.Range.Columns(idCol)
    Set r = r.Offset(1, 0).Resize(r.Rows.Count - 1, 1)
    Set r = r.SpecialCells(xlVisible)
r.Select
r.Copy
sh2.Activate
sh2.Range("A1").Select
sh2.Paste
End Sub

Thank you Tony. i have little different requirement. firstly, the filter used will be normal like excel -> Data tab -> filter. not a user input. user will select different check boxes under the filter pop up of excel, so there may be multiple selections like 5+ check boxes. how do i fetch these values and append my criteria that include blanks ? — chint, Aug 11 '17 at 06:00
Your comment here is much clearer than your original question. — Tony M, Aug 11 '17 at 10:34
If you use the macro recorder while defining filters as you describe, it will add code similar to what I provided. So, rather than determining the filters with code, you can learn to construct the filters yourself. For example, since you wanted to introduce blanks, I replaced the "Smith" at row 11 with a blank and then generated code to filter for both "Smith" & blanks. I then adapted the generated code to the variables defined in my code, and came up with the code needed to include blanks, which is: r.AutoFilter Field:=nameCol, Criteria1:="=" & chosenName, Operator:=xlOr, Criteria2:="=" — Tony M, Aug 11 '17 at 16:08

Excel Macro to capture selected filter criteria

1 Answers1

Linked