Macro AdvancedFilter without criteria

[opopanax666]

Hi guys,

I use following code to do a double filtering :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyValue1 As Integer
Dim MyValue2 As Integer

    MyValue1 = Sheets("x3").Range("dj1").Value
    MyValue2 = Sheets("x3").Range("dk1").Value
    
    Range("ct1:cv1100").Select
    
    Range("ct1:cv1100").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "cx1:cx2"), CopyToRange:=Range("cz1:cz1100"), Unique:=True
    Range("ct1:cv1100").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "cy1:cy2"), CopyToRange:=Range("da1:da1100"), Unique:=True
        
    Range("co1:cr1100").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "cz1:cz" & MyValue1), CopyToRange:=Range("dc1:de1100"), Unique:=True
    Range("co1:cr1100").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "da1:da" & MyValue2), CopyToRange:=Range("df1:dh1100"), Unique:=True
        
    Range("a1:a1").Select
End Sub

So the output of the first filter is used as a criteria for the second. It all works well, except when one of the first filters doesn't return any values. The second filter then returns all the values from its range instead of none...

I'm a total noob when it comes to VB, but I'm sure someone else has had the same problem.

Thanks for any feedback

 

[mikej336]

if MyValue1 <> "" then

Range("co1:cr1100").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"cz1:cz" & MyValue1), CopyToRange:=Range("dc1:de1100"), Unique:=True

end if

Uncle Mike