How to do skip an Autofilter action if a file is empty

Hi Guys, I have a Macro that puts an Autofilter on Row 3 of a text file on the "Differences" column for Values greater than 1 or less than 1, it then does End(xlDown) and copies and pastes the data. One text file is neraly always empty though so prevent it oveflowing the Macro I put in the expression CurrentRegion.Copy. However, what I really need it to do is not copy anything if there's nothing in the "Differences" column. How do I do this? I enclose the text file in question. Here's my code:
 Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\11_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "CreditBSRec_Daily_" & PrevDay & ".xlsx"

 
     Rows("3:3").Select
    Application.CutCopyMode = False
     Selection.Copy
    ActiveSheet.Range("$A$3:$R$50").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"

    Range("A4:r4").CurrentRegion.Select

    Selection.Copy

    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate


    Set target5 = Range("C5").End(xlDown).Offset(1)

     target5.PasteSpecial xlPasteValues
CreditBSRec-Daily-20131106-05355.txt
JustincutAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Perhaps something like this.

Dim rng As Range
Dim Res As Variant

     Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\11_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "CreditBSRec_Daily_" & PrevDay & ".xlsx"
     
      ' find differences column

      Res = Application.Match(Rows(3), "Differences", 0)

      If Not IsError(Res) Then
           If Cells(Rows.Count, Res).End(xlUp).Row <= 3 Then
                ' no data to filter
                Exit Sub
           End If
      End If

      Rows("3:3").Select
    Application.CutCopyMode = False
     Selection.Copy
    ActiveSheet.Range("$A$3:$R$50").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"

    Range("A4:r4").CurrentRegion.Select

    Selection.Copy

    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate


    Set target5 = Range("C5").End(xlDown).Offset(1)

     target5.PasteSpecial xlPasteValues 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.