Finding Unique Values in Named Range

Hello forum, I have a workbook we use to count inventory. My first issue which seems to be the biggest problem is that I am trying to find Unique values in a named range. The problem is that it is taking forever to run. I have 8 named ranges on each work sheet named ZONE_101, ZONE_102..... up to ZONE_108. It takes about 3 minutes for the value to be returned just for one range. Is there a better way to do this or a solution in VBA? I am using the following formula.I am not able to post the data I am using do to privacy restrictions, but each range is anywhere from 20k rows to 75k rows so the ranges are dynamically named. I will just post the formula for one range but I am doing it 8 times and adding the values together.

Please Login or Register  to view this content.

There are a couple of ways to do this. Since you didn't specify what you wanted done with the unique values I will assume you want them copied to another sheet (Sheet2 in this case). I also assumed each named range had a header which you didn't want counted.

Please Login or Register  to view this content.

I just need a total count of unique values. For example once inv is downloaded there are 57k bins we use. However there are only 29k unique values in that list.

jonboy6257,

never post confidential informations.
But you can alway create example workbook, remove extra data and change data to hide sensitive informations.
For exampe change names into aaaaa cccccc and prices into 9999.99 etc.

I should be able to recreate some data here in a few.

This will give you the counts within each named range. Once again assuming there are headers:

Please Login or Register  to view this content.

Thank you for your replies. I may be switching to do the entire report in Access at some point as I can run a few queries and get the numbers I want.
Thanks again,
Jon

If this is so slow Jon, why don't you side step the issue?

Store your Unique list. And add to that list when you have to.

Because of how often the list changes unfortunately. The warehouse I am doing this for is a logistical nightmare. They have a single bin with multiple items in each one. The list is huge anywhere from 300000 to 750000 unique items. There would be too much work editing the unique list. Plus with this data, I also have the item numbers stored, and the qty of items which is constantly changing. I will change this to solved. I have since stored the data in excel and written a macro to import into access where I can use SQL and create the custom reports I need more efficiently.
Thanks,
Jon