auto filter criteria count

**crazyqq** · 05-24-2011, 09:19 PM

Hello Masters,

have a quick question. I wish to count the individual criteria in the autofilter. I'd appreciate for your generous help.

here is a example

A B C

apple red big
apple green small
orange yellow big
pears purple mediumn

I wish to konw how many unique items in each column. So the answer should be 3, 4, 3

Thanks in advanced.

**MarvinP** · 05-24-2011, 09:54 PM

Hi Crazuqq and welcome to the forum,

Find the attached with some "helping" columns and the answer I think you desire.

**crazyqq** · 05-24-2011, 10:24 PM

Brilliant! Thanks MVP...

Now I need to think a way to code it in vba without showing the helping "columns".

**MarvinP** · 05-24-2011, 11:08 PM

Hi,

I saw an example a few months ago where a guy used a Collection Object. The idea is that he kept adding strings to the collection but if it was already there it wouldn't add a new object. Then he knew the unique number of objects by counting how many were in the collection.

Perhaps you can use this kind of code. I'm sure it was on this site but can't find it now.

**crazyqq** · 05-30-2011, 10:38 AM

Thanks!

Finally, I found an easier way to do it, just want to share

select the range, and choose tools-> filter-> Advanced Filter -> Unique records only

then excel will paste the list into a new column with unique record.