CODEWITHSUNDEEP
vbaexcel
Unicorn
Unicorn

Reputation: 295

Dynamic Criteria - Filtering Excel VBA

I need to filter on the same criteria, but the values of the criteria are not always the same in the data that I receive, so they need to be dynamic.

for eg.

Dim crit1 as String crit1 = ?

So that:

Selection.AutoFilter Field:=4, Criteria1:=crit1

here is my code

enter code herexDim TaskType, Status, Elapse As Long
Dim Total, Completed As Variant
Total = Array("COMPLETED", "ERROR", "KILLED")
Completed = Array("COMPLETED")
TaskType = WorksheetFunction.Match("tasktypeid", Rows("1:1"), 0)
Status = WorksheetFunction.Match("status", Rows("1:1"), 0)
Elapse = WorksheetFunction.Match("elapse", Rows("1:1"), 0)
'Use Filter Criteria
'100 Total
With Sheets("Raw_Data")
Set rnData = .UsedRange
With rnData
.AutoFilter field:=TaskType, Criteria1:="100"
.AutoFilter field:=Status, Criteria1:=Total, Operator:=xlFilterValues
.Select
        For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
        lcount = lcount + rngarea.Rows.Count
        Next
        a = lcount - 1
   End With
      End With
'100 Completed
    With Sheets("Data")
    Set rnData = .UsedRange
    With rnData
    .AutoFilter field:=TaskType, Criteria1:="100"
    .AutoFilter field:=Status, Criteria1:=Completed, Operator:=xlFilterValues
    .Select
            For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
            lcount1 = lcount1 + rngarea.Rows.Count
            Next
            b = lcount1 - 1
       End With
          End With

'101 Total
    With Sheets("Raw_Data")
    Set rnData = .UsedRange
    With rnData
    .AutoFilter field:=TaskType, Criteria1:="101"
    .AutoFilter field:=Status, Criteria1:=Total, Operator:=xlFilterValues
    .Select
            For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
            lcount2 = lcount2 + rngarea.Rows.Count
            Next
            c = lcount2 - 1
       End With
          End With
'101 Completed
    With Sheets("Data")
    Set rnData = .UsedRange
    With rnData
    .AutoFilter field:=TaskType, Criteria1:="101"
    .AutoFilter field:=Status, Criteria1:=Completed, Operator:=xlFilterValues
    .Select
            For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
            lcount3 = lcount3 + rngarea.Rows.Count
            Next
            d = lcount3 - 1
       End With
          End With

In above code i have calculated criteria1:= as 100,101 statically but it should take dynamic value present in the filter. Thanks in advance.

Upvotes: 1

Views: 9152

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

For example, Using my macro recorder to filter Column "I" for items that contain "a", I would get this kind of code.

Sub Macro5()
'
' Macro5 Macro
'

'
    Columns("I:I").Select
    Selection.AutoFilter
    ActiveSheet.Range("$I$1:$I$7").AutoFilter Field:=1, Criteria1:="=*a*", _
                                              Operator:=xlAnd
End Sub

Now I see how the code uses wildcards and I can now edit the code to use wildcards and my variable. My New code would look like this.

Sub FilterForA()
    Dim s As String
    s = "a"
    Columns("I:I").AutoFilter Field:=1, Criteria1:="=*" & s & "*"
End Sub

Upvotes: 1

Related Questions