Solved

Excel Autofilter problem

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
create excel pivot chart 12 43
VBA Help 18 44
Why do I often "Too many different cell formats" error message in Excel2010? 2 24
Msgbox tickler 10 23
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.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

773 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