Autofilter criteria's

Thu, 07 Sep 2006 13:11:07 Z

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

Autofilter criteria's

Sat, 09 Sep 2006 08:11:33 Z

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

Autofilter criteria's

Sun, 10 Sep 2006 08:03:07 Z

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:)

Autofilter criteria's

Wed, 29 Nov 2006 11:42:33 Z

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

Autofilter criteria's

Wed, 29 Nov 2006 12:26:44 Z

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

Autofilter criteria's

Wed, 29 Nov 2006 12:49:30 Z

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

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

Autofilter criteria's

Thu, 14 May 2009 09:06:20 Z

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

Autofilter criteria's

Thu, 14 May 2009 11:02:55 Z

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