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

 

[Wafee]

Just change the MX to NX.

Thank you mate?

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Wafee]

You're welcome & thanks for the feedback.

Hi Fluff,

Sorry to come back again but I am new this stuff.from the above code we discussed can we publish the list in to S column. Thanks in advance.

 

[Fluff]

Do you mean you want the list count in S2 instead of M2?

 

[Wafee]

Do you mean you want the list count in S2 instead of M2?

No, I want to see the whole list rather than the count.

 

[Fluff]

OK, how about

Range("S2").Resize(List.Count).Value = Application.Transpose(List)

 

[Wafee]

OK, how about

Range("S2").Resize(List.Count).Value = Application.Transpose(List)

Throwing the on-time error 1004: Apllication or object defined error. Is that something to do with Transpose? I am using 2013 version of ms office.

 

[Fluff]

What is the list count?

 

[Wafee]

What is the list count?

ListCount is the count of items in the List.

Below is the code I am using to calculate ListCount as suggested by you earlier.

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

 

[Fluff]

What is the actual value in M2 when you get the error?