Solved

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

Posted on 2013-11-07
1
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 34

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

738 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