CODEWITHSUNDEEP
vbaexcel
Ollie
Ollie

Reputation: 209

VBA skipping over a filter criteria

Hi I have the below code where I wish to use VBA to filter lists in Excel, Each list will be different depending on which company I select. 

With wsDest.Rows(1)
.AutoFilter field:=3, Criteria1:="Swap", Operator:=xlOr, Criteria2:="FwdSwap", Operator:=xlOr, Criteria3:="NDS"
  If wsDest.Range("C1:C" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
  wsDest.Range("G2:G" & lr).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("J" & Rows.Count).End(3)(2)
     End If
.AutoFilter
End With

wsDest is the worksheet I am working in, the code works fine except that lots of the lists i am filtering do not contain the "NDS" and the code breaks when this happens. Is there a way I can add into this 'if NDS exists in the list then use filter, else skip NDS as a filter'

Many thanks in advance

Upvotes: 0

Views: 476

Answers (1)

Carrosive
Carrosive

Reputation: 899

.AutoFilter field:=3, Criteria1:="Swap", Operator:=xlOr, Criteria2:="FwdSwap", Operator:=xlOr, Criteria3:="NDS"

Your syntax here is incorrect for filtering on multiple criteria. Although there is a Criteria2, there is no Criteria3 and so on for this method.

Where you want to filter for multiple values, you should instead use an array with Criteria1:

.AutoFilter Field:=3, Criteria1:=Array("Swap", "FwdSwap", "NDS"), Operator:=xlFilterValues

Also note the change in Operator to xlFilterValues as pointed out by @YowE3K

Upvotes: 2

Related Questions