Solved

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

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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

777 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