Set filter and loop across columns

[coop123]

Hi

I have a spreadsheet which has data across 200 columns. this data needs to be filtered on each column and the the totals copied out.

I have written a macro which set the filters and copies data to relevant position, my problem is how to I get the Field number within the filter to change each time a new column is selected.

Thanks in advance.

Coop123

 

[offthelip]

This shows you how to do that:

Sub Fitr()
   For i = 1 To 10
   ActiveSheet.AutoFilterMode = False
    Columns("A:Q").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$Q$32").AutoFilter Field:=i, Criteria1:=">10", _
        Operator:=xlAnd
    MsgBox ("Filtered on Column " & i)
   Next i
End Sub

 

[coop123]

Hi offthelip

thank you for your reply I put into my macro and it partially solved my problem.

I forgot to mention previously that I need to filter every third column not colums next to each other. Is there a way of adding this increment to the code you supplied.

Thanks

 

[maabadi]

Change

For i = 1 To 10

to

For i = 3 To 10 Step 3

 

[coop123]

Thank you that has solve my problem.

coop123

 

[maabadi]

Your welcome & Glad we can help.