Solved

Excel Autofilter problem

Posted on 2013-10-25
2
323 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 35

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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…

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