Copy filtered range

I have a routine that copies the visible cells in an autofilter range to another sheet, based on the criteria of how much data is revealed by the autofilter. I use this routine multiple times, and it works without a hitch, except in this instance. In this instance, it copies all of the available data, not only the visible cells. I've been trying to find out what the problem is, to no avail, and so am asking for help.

The code is below. The macro first filters column P, and pulls up anything that's not "UNAU". Then it filters column M for anything other than "G" or "E". The expected result is that nothing will be found, and that's what happens in this case. So, my autofilter is only displaying the headings. There's a test (Lcount) to see if the # of visible cells (including headers) is less than or greater than 20. If less, nothing is copied. If more, all of the visible cells, except for the headers, are supposed to be copied and pasted into my "Results" worksheet. But for some reason, even though the autofilter displays no results, my "Results" page is filled with ALL of the data that the autofilter is filtering on.

Help! It's driving me crazy!

Sub Test()
Dim DstWkb As Workbook
  Dim rng As Range
  Dim RngEnd As Range
  Dim cell As Range
  Dim NumRows As Variant
  Dim lcount As Variant
  Dim rTable As Range
  Set rng = Selection
  
Set DstWkb = Workbooks("Active Position Checklist.xls")
                     

      DstWkb.Worksheets("APRData").Select
        With DstWkb.Worksheets("APRData")
                
         'Restrain the filter to cells from A1 to the last entry in column Q
            Set rng = .Range("A1:Q1")
            Set RngEnd = .Cells(Rows.Count, rng.Column).End(xlUp)
            Set rng = IIf(RngEnd.Row < rng.Row, rng, .Range(rng, RngEnd))

'3)    Test to see if UNAU doesn't have a G or E
    rng.EntireRow.AutoFilter Field:=16, Criteria1:="=UNAU", Operator:=xlAnd
    rng.EntireRow.AutoFilter Field:=13, Criteria1:="<>G", Operator:=xlAnd, _
        Criteria2:="<>E"
    
    'See whether there's data or not
        lcount = .AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible).Count
                
        If lcount > 20 Then
        
        'Copy and paste only the filtered data
            Set rTable = Sheets("APRData").AutoFilter.Range
            Set rTable = rTable.Resize(rTable.Rows.Count - 1)
        'Move new range down to start at the first data row.
            Set rTable = rTable.Offset(1)
            rTable.Copy Destination:=DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)
         End If
End With

End Sub

This is the approach I use, not as elegant as yours but reliable for me:

lcount = .Cells(.Rows.Count, 13).End(xlUp).Row

If lcount > 1 Then .Range("A2:A" & lcount).EntireRow.Copy _
    DstWkb.Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(1)

Originally Posted by jomili

Thanks for the help. I haven't tried it yet (BUSY DAY!), but before I do I'd like to understand it more. What does the "13" do in the "Cells(Rows.Count" section? Is that relating to column M? So then the "IF LCount > 1" would check to see if there's only 1 entry in M, and if so it wouldn't copy, but if there's more than 1, it would copy?

Thanks for your help, and thanks in advance for your explanation.

Yes, the 13 is for column M, the last column you Autofiltered, which is why I used it. You could probably use any column in the dataset...

Since your headers/titles are in row1, if you filter two columns and ALL rows end up hidden, then the .End(xlUp) method I used on column M jumping up from the bottom of the column will fly all the way up to row 1. So if lcount = 1 then we know no other rows are visible, so we skip copying.

If lcount > 1 then we know some rows are visible, so we just copy from row 2 down to lcount...Autofiltered data will only copy visible rows in this instance so you're good to go.

Hello ,

Your problem may lie with the SpecialCells method. All data will be copied, or deleted whenever there are more than 8192 individual range areas. An area is a group of separate cells. For example, Range("A1:C10, F5:10") represents 2 areas.

Your statement

    'See whether there's data or not
        lcount = .AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible).Count

is actually returning the cell count in column "A:Q" that have been filtered and not the row count. To return the row count of the filtered cells, the Range property needs to follow SpecialCells.

    'See whether there's data or not
        lcount = .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.Count