VBA Autofilters

[dan8825]

Hey!

I've seen this thread which seems to be what I'm after, but doesn't quite work for me...

https://www.mrexcel.com/forum/excel-questions/593812-macro-autofilter-skip-if-blank.html

When I use the answer here it skips even if there is a field present. Below is what I have, there are several filters in play it is just the last one which is the issue where it is text specific. This could be one of 8 variants but will not always be there based on the previous filters.

Any help greatly appreciated!




ActiveSheet.Range("$A$1:$V$3000").AutoFilter Field:=13, Criteria1:="Y"
ActiveSheet.Range("$A$1:$V$3000").AutoFilter Field:=15, Criteria1:="<=0", _
Operator:=xlAnd
ActiveSheet.Range("$A$1:$V$3000").AutoFilter Field:=14, Criteria1:=">0", _
Operator:=xlAnd
ActiveSheet.Range("$A$1:$V$3000").AutoFilter Field:=3, Criteria1:=">0", _
Operator:=xlAnd
ActiveSheet.Range("$A$1:$V$3000").AutoFilter Field:=10, Criteria1:="Dubai Air", _
Operator:=xlAnd




Range("B2:C3000").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
Sheets("Sheet1").Copy

 

[Fluff]

Hi & welcome to MrExcel.
How about

   With ActiveSheet.Range("$A$1:$V$3000")
      .AutoFilter Field:=13, Criteria1:="Y"
      .AutoFilter Field:=15, Criteria1:="<=0"
      .AutoFilter Field:=14, Criteria1:=">0"
      .AutoFilter Field:=3, Criteria1:=">0"
      .AutoFilter Field:=10, Criteria1:="Dubai Air"
      If .SpecialCells(xlVisible).Count = 22 Then Exit Sub
   End With
   Range("B2:C3000").Copy
   Sheets("Sheet1").Range("A2").PasteSpecial xlPasteValues

 

[dan8825]

Hi Fluff,

Thanks for the quick response! doesn't seem to have worked, I also want it to go onto the search for a new word instead of "Dubai Air" rather than end the macro. Much like the thread I mentioned seems to do


Thanks!


With ActiveSheet.Range("$A$1:$V$3000")
.AutoFilter Field:=13, Criteria1:="Y"
.AutoFilter Field:=15, Criteria1:="<=0"
.AutoFilter Field:=14, Criteria1:=">0"
.AutoFilter Field:=3, Criteria1:=">0"
.AutoFilter Field:=10, Criteria1:="Dubai Air"

If .SpecialCells(xlVisible).Count = 22 Then Exit Sub
End With


Range("B2:C3000").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
Sheets("Sheet1").Copy

 

[Fluff]

In what way didn't it work?
Also when posting code please use code tags, the # icon in the reply window.

 

[dan8825]

Where Dubai Air is not present in the filter, it selects nothing and the other filter options I do not want present are selected

 

[dan8825]

Full list reads as below.

Where the previous filters rule an option out I would like it to skip to the next one, the macro I have pasted just repeats itself except changing the last text filter to one of the below.

Hope I'm being clear, Thanks!

Chile Air

Peru Air

Singapore Sea

Singapore Air Dubai Air

<colgroup><col></colgroup><tbody>
</tbody>

 

[Fluff]

Maybe

  With ActiveSheet.Range("$A$1:$V$3000")
      .AutoFilter Field:=13, Criteria1:="Y"
      .AutoFilter Field:=15, Criteria1:="<=0"
      .AutoFilter Field:=14, Criteria1:=">0"
      .AutoFilter Field:=3, Criteria1:=">0"
      .AutoFilter Field:=10, Criteria1:="Dubai Air"
      If .SpecialCells(xlVisible).Count > 22 Then
         Range("B2:C3000").Copy
         Sheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
      End If
      .AutoFilter Field:=10, Criteria1:="Chile Air"
      If .SpecialCells(xlVisible).Count > 22 Then
         'do something
      End If
      'etc
      'etc
   End With

 

[dan8825]

I have not used end with before.. so not sure the limitations, Is below what the full code should look like for two opitons?

As I get error invalid or unqualified reference

Sub Macro1()'
' Macro1 Macro
'


'  With ActiveSheet.Range("$A$1:$V$3000")
      .AutoFilter Field:=13, Criteria1:="Y"
      .AutoFilter Field:=15, Criteria1:="<=0"
      .AutoFilter Field:=14, Criteria1:=">0"
      .AutoFilter Field:=3, Criteria1:=">0"
      .AutoFilter Field:=10, Criteria1:="Dubai Air"
      If .SpecialCells(xlVisible).Count > 22 Then
         Range("B2:C3000").Copy
         Sheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
      End If
      .AutoFilter Field:=10, Criteria1:="Chile Air"
      If .SpecialCells(xlVisible).Count > 22 Then
         'do something
   End With

 

[Fluff]

Oops, it should be

  With ActiveSheet.Range("$A$1:$V$3000")
      .AutoFilter Field:=13, Criteria1:="Y"
      .AutoFilter Field:=15, Criteria1:="<=0"
      .AutoFilter Field:=14, Criteria1:=">0"
      .AutoFilter Field:=3, Criteria1:=">0"
      .AutoFilter Field:=10, Criteria1:="Dubai Air"
      If .SpecialCells(xlVisible).Count > 22 Then
         [COLOR=#ff0000].Parent.[/COLOR]Range("B2:C3000").Copy
         Sheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
      End If
      .AutoFilter Field:=10, Criteria1:="Chile Air"
      If .SpecialCells(xlVisible).Count > 22 Then
         'do something
      End If
      'etc
      'etc
   End With

As I don't know what you want to do for the other options I can't show how to do it.

 

[dan8825]

Thanks, I think this seems to being doing the job..

I will fill in everything else I need and fingers crossed.

Thanks very much, have a great Christmas!