Solved

Filter and Copy question

Posted on 2013-11-18
1
211 Views
Last Modified: 2013-11-18
Hi Guys, I have a text file that I open every day and filter in the 3rd row on the Differences column for anything bigger than 0 and then copy and paste it. I am currently using Current Region, instead of EndXl(Down) as the file usually has no differences, but I do not wish to copy and paste rows 1 and 2. My code is this:

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

Is there a way I can just copy the 3rd row if there are no differences. I enclose the file,
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 6

Accepted Solution

by:
Michael earned 500 total points
ID: 39656306
The following code will take your Range("A4:r4").CurrentRegion and exclude the first two rows:

Set rng = Range("A4:r4").CurrentRegion
Set rng = rng.Resize(rng.Rows.Count - 2).Offset(2)

Open in new window

Then, you can use rng.copy and paste it to the desired location.

Joop
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

734 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