Solved

Excel Autofilter problem

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now