Data loss when deleting filtered rows

[SammyCRX]

Hi all,

I've got a macro which appeared to be working perfectly, however when I ran the data without any of the products to be excluded I found that I was losing EVERYTHING I've narrowed the culprit down to this:

    'Remove excluded products    Sheets("Consolidated").Select
    DataArea = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$" & DataArea).AutoFilter Field:=12, Criteria1:="1"
    Cells.Select
    Selection.Copy
    Sheets("Removed Products").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consolidated").Select


'Deletes removed products from Consolidated tab
With ActiveSheet.AutoFilter.Range
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
End With


'Remove autofilter
    Application.CutCopyMode = False
    Selection.AutoFilter

Or more specifically, it appears to be this:

With ActiveSheet.AutoFilter.Range    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
End With

Please could someone advise if there's better code I could be using which won't result in everything being deleted should nothing match the criteria ("1") for products to remove? Any help would be much appreciated

Many thanks,
Sam

 

[Fluff]

Try

ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete

 

[SammyCRX]

Thanks, that's absolutely perfect

 

[Fluff]

You're welcome & thanks for the feedback