Solved

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

Posted on 2013-11-07
1
186 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

821 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