1

In excel File A, Col A i have family type for a parts list. 0.5V, 2V, 5V,10V,12V, etc in Col B thru K I have some part data dimensions and things like that. I was looking for a way to have a macro run a filter by the values in Col A, then run other code I already have, clear the filter selection, and then go to the selection option in the filter.

so if my filter options were

.1
.5
.03
.9
2
8

it would filter for .1 , run code, clear filter, filter by .5, run code, clear filter, filter by .03 etc etc. I am total lost on how to cycle thru the filter list. Any help would be much appreciated

CallumDA
  • 12,025
  • 6
  • 30
  • 52
Alberto Brown
  • 345
  • 1
  • 7
  • 24

1 Answers1

1

create an array of the values then cycle through that array.¹

Sub sequentialAutoFilter()
    Dim a As Long, arrs As Variant

    arrs = Array(0.1, 0.5, 0.03, 0.9, 2, 8)

    With Worksheets("Sheet99")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            For a = LBound(arrs) To UBound(arrs)
                .AutoFilter field:=1, Criteria1:=arrs(a)
                With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                    If CBool(Application.Subtotal(103, .Cells)) Then
                        'there are filtered cells visible, run code here
                        'note: .Cells does not include the header row here
                    End If
                End With
            Next a
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

End Sub

¹ Quoted from Scott Craner's comments.

Community
  • 1
  • 1
  • 2
    I like this. I provide the narrative and you provide the code. Easier on me. :) – Scott Craner Mar 21 '17 at 18:43
  • @jeeped I like this approach, the only issue is the array is about 500+ items long, and constantly growing. I'm currently hunting down a method to extract all unique values from col A. Those unique values would be the array . Back to google to find that lovely bit of code. once I get that all together I will run your code and see if everything works – Alberto Brown Mar 21 '17 at 18:49
  • @AlbertoBrown - just build a dictionary of unique keys and cycle through the keys instead of the array. See [Delete rows based on other sheet criteria](http://stackoverflow.com/questions/42752494/delete-rows-based-on-other-sheet-criteria/42752958?s=7|1.0056#42752958) –  Mar 21 '17 at 18:52
  • 1
    @AlbertoBrown check http://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array the top answer has a function that I use all the time. Loop through Column A and skip adding it to the array if it's already there. – BerticusMaximus Mar 21 '17 at 20:34
  • I'm still working on the full code but I did find a way to create a list of unique values from the A col. Now for the hard part. comparing each set of filtered results against anothe list and adding any in new values https://www.mrexcel.com/forum/excel-questions/649576-extract-unique-values-one-column-using-visual-basic-applications.html – Alberto Brown Mar 22 '17 at 14:06