Solved

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

Posted on 2013-11-07
1
156 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:Justincut
1 Comment
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 39630461
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now