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]

Glad to help & thanks for the feedback.

If you encounter any problems, just post back.

 

[dan8825]

Hello Fluff,

Below is the code, as you can see it filters data, takes this data to a new tab then saves this tab in another location as workbook, and repeat for each city I have listed in filter field 10.

This works perfectly except when there are no results in the filter when selecting a certain city. For example if you select London and there are no results, it will copy blank information into the tab and save a blank version and move onto the next city.

Is there a way for it to realise that London has no results, then move onto the next city rather than saving a London file?

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"
      If .SpecialCells(xlVisible).Count > 22 Then
         .Parent.Range("B2:C3000").Copy
         Sheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
        
          Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Sheets("Sheet1").Copy
   
    ActiveWorkbook.SaveAs Filename:= _
        "W:\data\International\\Orders\ ORDER\2.5 SOURCING\Uploads\Dubai DCD.xml" _
        , FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup _
        :=False
    ActiveWindow.Close
   
   
        Range("A2:B3000").Select
    Selection.ClearContents
 
        
        
      End If
      .AutoFilter Field:=10, Criteria1:="London"
      If .SpecialCells(xlVisible).Count > 22 Then
      .Parent.Range("B2:C3000").Copy
         Sheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
        
          Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Sheets("Sheet1").Copy
   
    ActiveWorkbook.SaveAs Filename:= _
        "W:\data\International\\Orders\ ORDER\2.5 SOURCING \Uploads\London DCD.xml" _
        , FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup _
        :=False
    ActiveWindow.Close
 
 
 
      End If
 
   End With
End Sub

 

[Fluff]

What is the last column of data in your sheet?

 

[dan8825]

In total there are 22 columns, so column V is last

That column is not an active filter it is just other data I use - will always be a number either 1 or 2

 

[Fluff]

In that case the code should work, assuming that you have a header in row 1, with the data starting in row 2

 

[dan8825]

Ok, odd doesn't seem to be, row 1 is the headers and data does start in row 2

I assume is the below that does that

[COLOR=#333333]If .SpecialCells(xlVisible).Count > 22 Then[/COLOR]

What does the 22 represent?

 

[Fluff]

The 22 is the number of visible cells (ie A1 to V1) in row1
So if there is no data for "London" only row 1 should be visible, meaning the count of visible cells would be 22 & the code should jump to the End If.

 

[Fluff]

Is there any particular column that will always have data to the last used row?

 

[dan8825]

Does seem to be working now... not sure what was happening before.

Hopefully is all perfect, will play test some more as I add in more cities. Thanks for help so far!

 

[Fluff]

For reference rather than copying the code for each city you can do something like

Sub Macro1()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Dubai", "London", "Oslo")
   For i = 0 To UBound(Ary)
      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:=Ary(i)
         If .SpecialCells(xlVisible).Count > 22 Then
            .Parent.Range("B2:C3000").Copy
            Sheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            Sheets("Sheet1").Copy
            
            ActiveWorkbook.SaveAs fileName:= _
               "W:\data\International\\Orders\ ORDER\2.5 SOURCING\Uploads\" & Ary(i) & " DCD.xml" _
               , FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup _
               :=False
            ActiveWindow.Close
            Sheets("Sheet1").Range("A2:B3000").ClearContents
         End If
      End With
   Next i
End Sub