Store active Autofilter criteria in array or range

[Yard]

Hi all,

I want to perform some operations (basically a secondary filter) based on the values which are currently filtered within a single filter column.

.Autofilter.Filters(n).Criteria1

and

.Criteria2

are great, but what if there are more than 2???

i.e. I have a column containing values L01 to L20.

My column is filtered on L05, L06 and L07 (or some other combination).

I want to extract the values and L05, L06 and L07 and do what I will with them.

What am I missing??

Thanks

 

[Richard Schollar]

This is the general idea with collections:

Sub Get_Uniques()
Dim coll As New Collection
Dim i As Long, cell As Range, temp As String
On Error Resume Next
For Each cell In ActiveSheet.AutoFilter.Range.Columns(1).Offset(1).SpecialCells(xlCellTypeVisible)
    If cell.Value <> "" Then
        coll.Add cell.Value, CStr(cell.Value)
    End If
Next cell
For i = 1 To coll.Count
    temp = temp & vbCrLf & coll(i)
Next
MsgBox "You have " & coll.Count & " unique items whose values are:" & temp
End Sub

which iterates thru the items in the first column of the autofilter range.

 

[Yard]

Awesome.

*rushes off, moistened, and forgets about this project and immediately starts applying new knowledge to other things*


**rushes back to PC in the hope that Rory has found some property of the Excel 2007 Autofilter**

 

[RoryA]

Sorry, I thought from your last post you had fixed the problem. Is that not the case?

 

[Yard]

Hmm it does read like that, doesn't it?

I can certainly use Richard's solution to give me what I need, but am still curious (=frustrated) about my inability to interrogate the settings within the 2007 Autofilter itself. The multiple selections must exist in the application somewhere....but where?

I'm guessing that on larger sets of data, extracting the Autofilter criteria must be quicker than looping through every cell which has been filtered, hence my thirst for knowledge...

 

[RoryA]

I think you set them by specifying an array of values for criteria1 so it may be that that returns an array or delimited string?

 

[James Snyder]

Necro answer:

                    .AutoFilter Field:=wkColumn, Criteria1:=Array( _
                        param2, _
                        param3, _
                        param4), _
                        Operator:=xlFilterValues