VBA Code to get distinct count with muliple criteria

[Wafee]

Can some one help me with a VBA code that gives distinct count of data in in "I" Column of sheet1 (dynamic) after filtering "Temp" and "Tem" in A column and "MX" in E column. I have a code as below which gives distinct count from "I" column but not able to add the filters. Would be great if get a code simpler than this.

Sub Dcount()
Dim Lastrow As Long
Rng As Range
List As Object
Listcount As Long
Lastrow = Cells(Rows.Count,"A").End(xlUp).Row
Set List = Createobject("Scripting.Dictionary)
For Each Rng In Range("I2:I", & Lastrow)
If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
Next
Listcount = List.Count
Range("M2").Value = ListCount

End Sub

 

[Fluff]

How about

For Each Rng In Range("I2:I", & Lastrow).Specialcells(xlVisible)

 

[Wafee]

How about

For Each Rng In Range("I2:I", & Lastrow).Specialcells(xlVisible)

Hi Fluff,
Sorry if my question was confusing but code has to filter and select "Temp" and "Tem" in A column and "MX" in E column and the calculate the distinct count.

 

[mumps]

Try:

Sub Dcount()
    Application.ScreenUpdating = False
    With Cells(1, 1)
        .CurrentRegion.AutoFilter Field:=1, Criteria1:="=Tem", Operator:=xlOr, Criteria2:="=Temp"
        .CurrentRegion.AutoFilter Field:=5, Criteria1:="MX"
    End With
    Range("M2") = [subtotal(103,A:A)] - 1
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

How about

Sub Dcount()
Dim Lastrow As Long
Dim Rng As Range
Dim List As Object
Dim Listcount As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set List = CreateObject("Scripting.Dictionary")
With Range("A1:I1")
   .AutoFilter 1, "Temp", xlOr, "tem"
   .AutoFilter 5, "MX"
End With
For Each Rng In Range("I2:I" & Lastrow).SpecialCells(xlVisible)
   List(Rng.Value) = Empty
Next Rng
ActiveSheet.AutoFilterMode = False
Listcount = List.Count
Range("M2").Value = Listcount

End Sub

 

[Wafee]

How about

Sub Dcount()
Dim Lastrow As Long
Dim Rng As Range
Dim List As Object
Dim Listcount As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set List = CreateObject("Scripting.Dictionary")
With Range("A1:I1")
   .AutoFilter 1, "Temp", xlOr, "tem"
   .AutoFilter 5, "MX"
End With
For Each Rng In Range("I2:I" & Lastrow).SpecialCells(xlVisible)
   List(Rng.Value) = Empty
Next Rng
ActiveSheet.AutoFilterMode = False
Listcount = List.Count
Range("M2").Value = Listcount

End Sub

Thank you that worked Perfectly, except at With Range("A1:I1") where used with Cells(1,1) instead.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Wafee]

Try:

Sub Dcount()
    Application.ScreenUpdating = False
    With Cells(1, 1)
        .CurrentRegion.AutoFilter Field:=1, Criteria1:="=Tem", Operator:=xlOr, Criteria2:="=Temp"
        .CurrentRegion.AutoFilter Field:=5, Criteria1:="MX"
    End With
    Range("M2") = [subtotal(103,A:A)] - 1
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Hi Mumps,

Can you help me with below requirement.

With the same filter in field 1, can we change criteria in field 5 to "NX" rather than "MX".

 

[Wafee]

You're welcome & thanks for the feedback.

Hi Fluff

Can you help me with below requirement.

With the same filter in field 1, can we change criteria in field 5 to "NX" rather than "MX". Thank you in advance.

 

[Fluff]

Just change the MX to NX.