AutoFilter Code: Show All Data

**StephenR** · Sep 5th 2006

I have some code for applying various filters, up to a maximum of five. However, not all five fields need necessarily have any filter but I can't find a way in the vaConditions line to show 'no filter' or 'All'. Can anyone help?

Code

vaFields = VBA.Array(1, 3, 4, 9, 10)
    vaConditions = VBA.Array("", "", "", ">20", "")
         
    Set rngData = Worksheets("CECO 2005").UsedRange
        
    For i = 0 To 4
        rngData.AutoFilter Field:=vaFields(i), Criteria1:=vaConditions(i)
    Next i

TIA

**StephenR** · Sep 5th 2006

Re: Autofilter - varying number of filters

I've thought of a rather obvious workaround: to put in a condition which will definitely capture all records. More elegant solutions gratefully received.

Dave Hawley · Sep 5th 2006

Re: Autofilter - varying number of filters

See these links on Excel VBA & AutoFilter

Excel VBA & AutoFilter AutoFilter provides us with a MUCH faster alternative to loops of all kinds

Excel VBA AutoFilter Criteria How to add the criteria for AutoFilter in VBA.

Excel VBA AutoFilter Dates Using dates in AutoFilter can be tricky if not using the US date format.

**StephenR** · Sep 5th 2006

Re: AutoFilter Code: Show All Data

Many thanks Dave. I'll look through those.

AutoFilter Code: Show All Data

Participate now!

Share