Autofilter criteria's
- 0Hello 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
All Replies
- 0
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 SubChas
- 0Hi 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:) - 0hey river...,
I have the same problem, did you found any solution ???
Thanks - 0
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 - 0hey good ideea,
Found something else when pressing the F1 magic key:
Columns("A:A").Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True - 0Hello 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- Proposed As Answer bycircuitman06 Thursday, May 14, 2009 11:01 AM
- 0I've found the answer Thank you for all,,
