Solved

Excel Autofilter problem

Posted on 2013-10-25
2
326 Views
Last Modified: 2013-10-27
Hi, I do a Daily Rec which means I have to auto-filter a list in an Excel spreadsheet and I copy and paste the contents to another spreadsheet. How can I exclude copying the top row of the block when I am copying and pasting (which are essentially just headings)? here's the code I currently use:

lr = Cells(Rows.Count, "A").End(xlUp).Row

Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$" & lr).AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"


    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
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
2 Comments
 
LVL 36

Expert Comment

by:Kimputer
ID: 39600002
Change

Range("A3").Select

to

Range("A4").Select
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 39600178
You can skip the selecting:

   ActiveSheet.AutoFilterMode = False
   LR = Cells(Rows.Count, "A").End(xlUp).Row

   With ActiveSheet.Range("$A$3:$R$" & LR)
      .AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
        ' check there are matching cells
        If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).Copy
   End With

Open in new window

0

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

631 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