After I filter a range using autofilter I would like to look at the number of unique entries in another field to run a loop.
The long and short of it is I need to do something different with each unique entry left after autofilter has completed.
Count
Selection.Autofilter field:=2
Any Ideas ???:)
I'm not sure I fully follow what you need, but if you are simply trying to geneate the list of unique entries in column B for a specific entry in column A, then you might consider using a pivot table instead of autofilter.
Let's say your Column A heading is Head1 and column B is Head2. Then create a piovot table using Head1 in the Page Field and Head2 in the Row field. Put Count of Head2 in the Data Field.
The pivot table will then in its column A list the unique Head2 values associated with whatever Head1 entry you select in the Page field. Column B of the pivot table (the data field) will tell you how many times each unqiue entry is asociated with that Head1 selection.
You should be able to use a Dynamic Named Range to pick off the unique value list for use in VBA.
I am trying to use the built in functionality apparent in autofilter.
When you pull down the criteria arrow it yields a list of unique entries for that column.
The basic utility of my macro is going to filter two fields which are going to be used to isolate the specific location.
However there can be up to 7 devices at each location which is where my third criteria comes in. In an attempt to avoid looping un-needed times i would like to know how many items are in the pulldown.
After filtering the first two fields I noticed that the third field criteria pull down was exactly what I need.
I would use pivots but they are two slow and cumbersome for my macro.
I filter my list of 65,000 records down and copy the result to a new sheet which is then used to as the datasource for my graphs.
You might try Advanced Filters. They will let you set the criteria you want and then produce a list of unique entries in the field that you specify.
Don’t have an account yet? Register yourself now and be a part of our community!
