VBA Filter Multiple Criteria

  • Hello everyone,


    I have a quick bit of code that I am having trouble with:

    Code
    1. Dim lrow As Integer
    2. lrow = Range("A65536").End(xlUp).Row
    3. ActiveSheet.Range("$A$1:K" & lrow).AutoFilter Field:=10, Criteria1:=RGB(220, _
    4. 230, 241), Operator:=xlFilterCellColor


    Above is what I have so far, I also want to add a criteria to this, so if the value is zero don't show.


    Overall, the line would read, if Field:= 10 RGB(220,230,241) then show and if Field:= 10 "<>0" then show


    Thanks everyone!

  • Re: VBA Filter Multiple Criteria


    Hi Djmoly


    I don't think you can filter on two criteria when it involves filtering by colour. The problem is that where you use xlcellcolor you are using the criteria reserved for the XlOr object. One workaround which I have tested is to create a conditional format in Col K and say if cell value >0 then colour = YOURCOLOUR HERE eg. Red. Then you can use the following line.


    Code
    1. Range("A1", Range("K65536").End(xlUp)).AutoFilter 10, RGB(220, 230, 241), xlFilterCellColor


    Take care


    Smallman

  • Re: VBA Filter Multiple Criteria


    Please use code tags per the Forum Rules you agreed to follow.


    [COLOR="navy"]How to use code tags[/COLOR]


    [noparse]

    Code
    1. [/noparse]
    2. [COLOR="navy"]your code goes between these tags[/COLOR]
    3. [noparse]

    [/noparse]


    Or, just highlight all of the code and press the [COLOR="#FF0000"]#[/COLOR] button to add the code tags