Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Filter and Copy question

Posted on 2013-11-18
1
Medium Priority
?
217 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
1 Comment
 
LVL 6

Accepted Solution

by:
Michael earned 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

572 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