Solved: Autofilter question

[uksrogers]

I have a Column to which i am applying an autofilter. I was then hoping to programatically loop through each item in the autofilter drop down list. I will then perform an action based on each item. Does anyone know if this is possible?

I have had a look at Filter and Filters, but from what i can see these only work if a filter has already been applied.

Thanks

uksrogers

[Zack Barresse]

Hi uksrogers,

That is a unique list which isn't readily available to the users. To get a unique list there are a few different methods you can use. A couple of ways are to use AdvancedFilter, which can be used to filter for uniques, and using a Dictionary object, which can be done completely via VBA.

Post back if you need more help.

[vonpookie]

As far as I know, there is no built-in way to reference the list that autofilter generates in the drop-down. You can write code to figure out what items would be in the list, though. I've done that before.

It's not the best code in the world, but here are some example codes that may help get you started:
http://www.mrexcel.com/board2/viewtopic.php?t=177857
http://www.mrexcel.com/board2/viewtopic.php?t=182145

[mdmackillop]

Here's a start using the suggested filter
[vba]Dim MyArray, m
Range("A1:" & [A1].End(xlDown).Address).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("G1"), _
Unique:=True
MyArray = Range("G1:" & [G1].End(xlDown).Address).Value
Range("G1:" & [G1].End(xlDown).Address).ClearContents
For Each m In MyArray
Debug.Print m
Next[/vba]

[uksrogers]

Thanks to everyone that took time to reply... I did have a go at the advanced filter bit and got that working in the most part. It was a bit odd though because even including the unique option, the first and last items were the same.

Is a shame though that you can't copy directly to an array instead of having to paste it into the worksheet and then putting it into the array...

Anyways... am sure i can work with what is available.

Thanks again

uksrogers

[mdmackillop]

You can create a unique listing using the Dictionay object
[VBA]
Option Explicit
Sub Unique()
'Reqires reference to MS Scripting Runtime
Dim a, d, i As Long, cel As Range
Set d = CreateObject("Scripting.Dictionary")
For Each cel In Range("A1:" & [A1].End(xlDown).Address)
On Error Resume Next
d.Add cel.Value, cel.Value
Next
a = d.Items 'Get the items
For i = 0 To d.Count - 1 'Iterate the array
Cells(i + 1, 3) = a(i) 'Print item
Next
End Sub

[/VBA]

[Zack Barresse]

MD just gave an example of what I meant by, "and using a Dictionary object, which can be done completely via VBA."