Autofilter criteria's

Question

Hello everyone,

This might be really simple but I couldn't find the answer for it.What I'm trying to achieve is that I need to find all the possible autofilter criteria's of a single Excel list field (Column) from VBA. I know I could just iterate over the column's cells and distnictly find the values and store them into an array, but I'd really be intrested to know if there is a property which already gives me that.

thanks,
Mohamed

Answer 1

Hello,

Something like the following is probably what you are looking for:

A1 should have a header for the columns

A2 downwards is your data.

[code]

Sub test()
 Range("A1").Select
 Selection.AutoFilter
 srchStr = "test5"
 Worksheets("Sheet1").Range("A1").AutoFilter field:=1, _
 Criteria1:=srchStr, VisibleDropDown:=False
End Sub

Chas

 

Answer 2

Hi Chas,

Thanks for your response. Sorry if my question is not clear i'll try to re-word it. What I need to do is that I have a sheet with a list, I need to split this sheet in seperate sheets based on filters of one of the column, assume the column name is "Application", from the VBA code where I don't know exactly what values might be under that column, I need to iterate over them first, exactly like what the filter arrow does for me which lists all the filter criteria's. So  I basically don't know what are filters and I need to look them up.

What property in Excel object model can give me this?

Thanks again:)

Answer 3

hey river...,
I have the same problem, did you found any solution ???
Thanks

Answer 4

Hi

You could always store the unique values of a column in a collection, below runs down column A until it hits a blank, then lists unique entries in the debug window:

 

Public Sub Listing()
Dim x As Long
Dim strList As New Collection
x = 1
With Worksheets("Sheet1")
While Len(.Cells(x, 1).Value) > 0
        On Error Resume Next
        strList.Add Item:=.Cells(x, 1).Value, key:=.Cells(x, 1).Value
        On Error GoTo 0
    x = x + 1
Wend
End With
If strList.Count > 1 Then
    For x = 1 To strList.Count
        Debug.Print strList(x)
    Next
End If
Set strList = Nothing
End Sub

Answer 5

hey good ideea,
Found something else when pressing the F1 magic key:

Columns("A:A").Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Answer 6

Hello ChasAA,

When excel shows the "test" filtered rows how can I copy all of them to a seperate sheet? I need to select all of test5 filtered rows and then copy to them to another sheet.

Dim srchStr As String
 
 
 Range("A11").Select
 Selection.AutoFilter
 srchStr = "CL15"
 Worksheets("Nisan1").Range("A1").AutoFilter field:=11, _
 Criteria1:=srchStr, VisibleDropDown:=False
 Selection.Copy??????????
 Sheets.Add
 ActiveSheet.Paste

Answer 7

I've found the answer Thank you for all,,