autofilter.filter.lists vba help

[smi123]

I really need help in VBA code for the list of items in an autofilter selection.

For example
Three columns Name, Town, Postcode - Autofiltered
Thousands of rows

If I click on the second field (town) a list would drop down to enable me to click on a town - What I need.... is the VBA code to go through each one of those items on the list, and loop to the next item in the list until there are no more items

Between each item on the list I will write some code.
This is to enable a summary to be compiled.

This is the last phase in totally automating a report from text upload to productive end result to summary in email.

So... would really appreciate some help.

 

[Von Pookie]

I can't figure out how to use the autofilter.filter thing myself (and to be honest, I'd never heard of it until I took a look in the help files--go figure).

But this seems to be working for me:

Dim Criteria As Variant
Sub test()
Dim i As Long, x As Integer
Dim myRng As Range

'range of data, not including header row (data starts in row 2)
Set myRng = Range("A2", Range("C65536").End(xlUp))

'check each item in "Town" list, add to Criteria variable
With myRng.Columns(2)
    Criteria = .Cells(1).Value
    For i = 2 To .Rows.Count
        If TextExists(.Cells(i).Value) = False Then
            Criteria = Criteria & "," & .Cells(i).Value
        End If
    Next i
End With
    
'make Criteria an array
Criteria = Split(Criteria, ",")

With Rows("1:1")
    .AutoFilter 'turn on autofilter
    
    'loop through Criteria
    For x = LBound(Criteria) To UBound(Criteria)
        'filter "Town" column using criteria
        .AutoFilter field:=2, Criteria1:=Criteria(x)
    
        With myRng.SpecialCells(xlCellTypeVisible)
            'run code on visible cells within myRng
        End With
    Next x 'display filter results for next item

    .AutoFilter 'turn off autofilter
End With

End Sub


Private Function TextExists(currText)
'   Returns TRUE if text already exists in the Criteria variable _
(no duplicates should be in list)
Dim x As Variant
On Error Resume Next
x = WorksheetFunction.Search(currText, Criteria)
If Err = 0 Then TextExists = True Else TextExists = False
End Function

 

[smi123]

Re Kristys reply to autofilter.filter.lists vba help

Thank you very very much Kristy
Works a treat!
I did not need that array part - but my grey cells are now ticking away!

Dim Criteria As Variant

Private Function TextExists(currText)
' Returns TRUE if text already exists in the Criteria variable _
(no duplicates should be in list)
Dim x As Variant
On Error Resume Next
x = WorksheetFunction.Search(currText, Criteria)
If Err = 0 Then TextExists = True Else TextExists = False

End Function

Sub test()
Dim i As Long, x As Integer
Dim myRng As Range

'range of data, not including header row (data starts in row 2)
Set myRng = Range("A6", Range("C65536").End(xlUp))

'check each item in "Town" list, add to Criteria variable
With myRng.Columns(3)
Criteria = .Cells(1).Value
For i = 6 To .Rows.Count
If TextExists(.Cells(i).Value) = False Then
Criteria = Criteria & "," & .Cells(i).Value

' My routine to summarise data from filtered results
Call my_routine(Criteria)
End If
Next i
End With

End Sub

 

[smi123]

Re My modification


instead of
Criteria = Criteria & "," & .Cells(i).Value

Used
criteria = .Cells(i).Value

Enabled me to just pass the actual town as did not need the array