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!

[TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Chile Air[/TD]
[/TR]
[TR]
[TD]Peru Air[/TD]
[/TR]
[TR]
[TD]Singapore Sea[/TD]
[/TR]
[TR]
[TD]Singapore Air
Dubai Air[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!