VBA - Filter/IF Stopping my Results

[ExcelMercy]

Hey Everyone,



I cannot seem to figure this out... I'm creating a tab with a macro. Pulling the specific items that I want. Yet, if there are NO results for the items I'm looking for, I get an error. Added an If to avoid it and it fixed the issue and the macro runs if there are NO results. However if there ARE results, the macro now skips over the results and leaves me with a blank sheet.



Here is the code with the IF commented out, this code works perfectly as long as there are results:

Sub Test1()
   'Creates X Validation Sheet
    
    Dim wsX1              As Worksheet
    Dim wsX2              As Worksheet
    Dim Xvar1             As Long
    Dim Xvar2             As Long
    Dim MA_X_Var          As Variant
    Application.ScreenUpdating = False
    
    Set wsX1 = Worksheets("Main_Tab")
    Set wsX2 = Worksheets.Add
    MA_X_Var = Array("X_1", "X_2")
    With wsX2
        .Name = "X_Items"
        .Move after:=Sheets(Sheets.Count)
        .Range("A1").Resize(1, 4).Value = Array("Code", "ID", "First Name", "Last Name")
    End With
    With wsX1
        If .AutoFilterMode Then .AutoFilterMode = False
        Xvar1 = .Range("D" & .Rows.Count).End(xlUp).Row
        Xvar2 = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
        
        

        .Cells(1, "D").Resize(Xvar1).AutoFilter Field:=1, Criteria1:=MA_X_Var, Operator:=xlFilterValues
  ' If .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
        .Range("D2").Resize(Xvar1 - 1).SpecialCells(xlCellTypeVisible).Copy
         wsX2.Range("A2").PasteSpecial xlPasteAll
        .Range("E2").Resize(Xvar1 - 1).SpecialCells(xlCellTypeVisible).Copy
        wsX2.Range("B2").PasteSpecial xlPasteAll
        .Range("G2").Resize(Xvar1 - 1).SpecialCells(xlCellTypeVisible).Copy
        wsX2.Range("C2").PasteSpecial xlPasteAll
        .Range("I2").Resize(Xvar1 - 1).SpecialCells(xlCellTypeVisible).Copy
        wsX2.Range("D2").PasteSpecial xlPasteAll
   'End If
End With
End Sub

Here is some mock data (with x_1 & x_2 Code) :

Type	Other	Order	Code	ID	Extra	First Name	Middle Initial	Last Name
HDW	fadfsad	0	x_6	86435	no	Billy	Z	Smith
ASD	fagasgda	1	x_1	12345	no	John	A	Willams
GDG	fdsaga	0	x_5	756890	no	Bob		Wilson
FSD	fdsafdsa	2	x_1	554321	no	Sue		Locke
GSD	gdsafads	3	x_2	642131	no	Thomas	Y	Doe

<tbody>
</tbody>

If you un-comment out the If.AutoFilter.Range (etc), the results will be an empty page.If I don't have an x_1 or x_2 in Code like:

Type	Other	Order	Code	ID	Extra	First Name	Middle Initial	Last Name
HDW	fadfsad	0	x_6	86435	no	Billy	Z	Smith
ASD	fagasgda	1	x_4	12345	no	John	A	Willams
GDG	fdsaga	0	x_5	756890	no	Bob		Wilson
FSD	fdsafdsa	2	x_5	554321	no	Sue		Locke
GSD	gdsafads	3	x_4	642131	no	Thomas	Y	Doe

<tbody>
</tbody>

I get Run-time error '1004' No cells were found. Unless I un-comment out If.AutoFilter.Range (etc), then it will run and not give me an error.






I'd greatly appreciate any help!

 

[dmt32]

hi,
not tested but see if this change to your code helps:

   Dim rng As Range, FilterRange As Long
    With wsX1
        If .AutoFilterMode Then .AutoFilterMode = False
        Xvar1 = .Range("D" & .Rows.Count).End(xlUp).Row
        Xvar2 = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
        .Cells(1, "D").Resize(Xvar1).AutoFilter Field:=1, Criteria1:=MA_X_Var, Operator:=xlFilterValues
        
        Set rng = .AutoFilter.Range


        'check there are some visible rows to copy
        FilterRange = rng.Columns(4).SpecialCells(xlCellTypeVisible).Count - 1


   If FilterRange > 0 Then


        .Range("D2").Resize(Xvar1 - 1).SpecialCells(xlCellTypeVisible).Copy
         wsX2.Range("A2").PasteSpecial xlPasteAll
        .Range("E2").Resize(Xvar1 - 1).SpecialCells(xlCellTypeVisible).Copy
        wsX2.Range("B2").PasteSpecial xlPasteAll
        .Range("G2").Resize(Xvar1 - 1).SpecialCells(xlCellTypeVisible).Copy
        wsX2.Range("C2").PasteSpecial xlPasteAll
        .Range("I2").Resize(Xvar1 - 1).SpecialCells(xlCellTypeVisible).Copy
        wsX2.Range("D2").PasteSpecial xlPasteAll


   End If
End With

Dave

 

[ExcelMercy]

Like a boss!



Thanks a ton!