If then, Go To Next Sub using a filtered column in VBA

[sschwant]

I have a macro with a 2 step process. 1) filter on Description col for any "AP Accruals", 2) filter on same col for any "Xfers". While there will always be AP Accruals, there will be times when there will not be any "Xfers" in the data. I need to figure out the right syntax or logic to say IF my Autofilter for Xfers is blank or "", then skip the rest of that routine and move onto the next one. B/c if it goes on as if there were some data there it screws up the rest of process (it deletes the 1st row of column headers, moves remaining rows up 1 in source data and then skips the first row of data (needed) in my final step where I move data to a new tab. This process works fine both step 1 and step 2 - IF the filter identifies any criteria in the column. This could an IF / End IF, and IF THEN, End IF or IF GO TO. Just not sure how to set up the block of code to say IF my filter is blank, then go to next sub or bottom of current sub . . . and skip the copy paste routine. Here's the key section of the code:

   Sub Move_Xfers_Out()
    Application.ScreenUpdating = True
    Sheets("Stage raw JE data").Select
    Range("Q1").Select
    Selection.AutoFilter
        ActiveSheet.Range("$A$1:$R$4062").AutoFilter Field:=17, Criteria1:= _
        "=*Xfers*", Operator:=xlAnd
    If Criteria1 = "*" Then GoTo 10
    End If
    ActiveCell.Offset(1, -16).Range("A1").Select
       Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
       Selection.Copy
    
'   Switch to IC Transfers tab
    Sheets("IC Transfers").Select
    Range("A2").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Stage raw JE data").Select
        Application.CutCopyMode = False
    Selection.EntireRow.Delete
    Selection.AutoFilter
10:    Call Copy_Stgd_JE_data_to_TblJE


End Sub

Thanks in advance for any help!!!

Tried using 10: as the identifier for the line for the GoTo statement .... but this is way off the mark I'm sure ... the issue seems to be finding the right syntax and right way to structure it around the filter ....

Steve

cross posted: http://www.mrexcel.com/forum/excel-q...ml#post4554714
and here: http://answers.microsoft.com/en-us/o...=1466288572575

[mdmackillop]

Why not simply do a Find on the column. If found then Autofilter.

[sschwant]

Why not simply do a Find on the column. If found then Autofilter.

What would that code logic / syntax look like? Something like this?

Col Q, Find "Xfers"

If Col Q = "", Then Go To Next Sub

Else Auto Filter (rest of my sub runs as is)

End If

End Sub

Thanks!

Steve

[mdmackillop]

Simplest to do it from another sub

Sub test()
Dim c As Range
Set c = Range("$A$1:$R$4062").Find("xfers", lookat:=xlPart)
If c Is Nothing Then
    Call OtherMacro
Else
    Call Move_Xfers_Out
End If
End Sub

[snb]

Use advancedfilter, it's been designed for this purpose.