VBA - Filter/IF Stopping my Results

ExcelMercy

ExcelMercy

Board Regular
Joined
Aug 11, 2014
Messages
151
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:

Rich (BB code): 
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) :


[TABLE="width: 432"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: lightsteelblue"]Type
[/TD]
[TD="class: xl63, width: 64, bgcolor: lightsteelblue"]Other
[/TD]
[TD="class: xl63, width: 64, bgcolor: lightsteelblue"]Order
[/TD]
[TD="class: xl63, width: 64, bgcolor: lightsteelblue"]Code
[/TD]
[TD="class: xl63, width: 64, bgcolor: lightsteelblue"]ID
[/TD]
[TD="class: xl63, width: 64, bgcolor: lightsteelblue"]Extra
[/TD]
[TD="class: xl63, width: 64, bgcolor: lightsteelblue"]First
Name
[/TD]
[TD="class: xl63, width: 64, bgcolor: lightsteelblue"]Middle
Initial
[/TD]
[TD="class: xl63, width: 64, bgcolor: lightsteelblue"]Last Name
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]HDW
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]fadfsad
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]0
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]x_6
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]86435
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Billy
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Z
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Smith
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]ASD
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]fagasgda
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]x_1
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]12345
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Willams
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]GDG
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]fdsaga
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]0
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]x_5
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]756890
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Bob
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Wilson
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]FSD
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]fdsafdsa
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]x_1
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]554321
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Sue
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Locke
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]GSD
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]gdsafads
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]3
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]x_2
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]642131
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Thomas
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Y
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Doe
[/TD]
[/TR]
</tbody>[/TABLE]



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:
[TABLE="width: 432"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: lightsteelblue"]Type
[/TD]
[TD="class: xl65, width: 64, bgcolor: lightsteelblue"]Other
[/TD]
[TD="class: xl65, width: 64, bgcolor: lightsteelblue"]Order
[/TD]
[TD="class: xl65, width: 64, bgcolor: lightsteelblue"]Code
[/TD]
[TD="class: xl65, width: 64, bgcolor: lightsteelblue"]ID
[/TD]
[TD="class: xl65, width: 64, bgcolor: lightsteelblue"]Extra
[/TD]
[TD="class: xl65, width: 64, bgcolor: lightsteelblue"]First
Name
[/TD]
[TD="class: xl65, width: 64, bgcolor: lightsteelblue"]Middle
Initial
[/TD]
[TD="class: xl65, width: 64, bgcolor: lightsteelblue"]Last Name
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]HDW
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]fadfsad
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]0
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]x_6
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]86435
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Billy
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Z
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Smith
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]ASD
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]fagasgda
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]x_4
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]12345
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]John
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Willams
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]GDG
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]fdsaga
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]0
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]x_5
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]756890
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Bob
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Wilson
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]FSD
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]fdsafdsa
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]x_5
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]554321
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Sue
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Locke
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]GSD
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]gdsafads
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]3
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]x_4
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]642131
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]no
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Thomas
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Doe
[/TD]
[/TR]
</tbody>[/TABLE]

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!
 
Sort by date Sort by votes
hi,
not tested but see if this change to your code helps:

Code: 
   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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA date filter returns blank
Replies
3
Views
539
Micron
M
N
  • Question Question
Increasing the Column Input Range on VBA Macro where Data is to be transposed into rows
Replies
0
Views
543
Nicw24
N
G
  • Solved
EDIT FILTER VBA
Replies
2
Views
681
gleamng
G
H
  • Solved
Call to second macro doesn't work if no results in original
Replies
2
Views
512
honkin
H
Navi_G
  • Question Question
How to apply my VBA Code on multiple workbooks in a folder
2 3
Replies
24
Views
2K
Navi_G
Navi_G

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top