Delete Filtered Rows Excluding The Header

[xander1981]

Hi, feel stupid as I have a feeling this is a simply question and I am doing something silly. I have the following code to filter a column between date range then delete all visible rows, then take off filter to display the remaining rows outside the date range. I use this code on another workbook with no problems as all. However on this workbook the code does the trick in deleting the range but also deletes the header!! though I am using the 'offset' function to avoid row 1. Not sure why and I have tried lots of work arounds with no luck. Any ideas?

With GTNreport.Sheets("PRG CFS Receipts")
    'DELETE LINES OUTSIDE OF WEEK WE NEED
    With Range(("J1"), Range("J1000")).End(xlUp)
          .AutoFilter 10, Criteria1:="<" & GetDateRangeFrom, Operator:=xlOr, Criteria2:=">" & GetDateRangeTo
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
         .Range("A1").Select
         Selection.AutoFilter
    End With

many thanks in advance

[xander1981]

Further to the above, i changed the .delete for .select and set the line after as a break point so I could see what was happening and it selected ALL cells (to be deleted), so I think the problem is with the 'With' range?

[p45cal]

Yes, your:
Range(("J1"), Range("J1000")).End(xlUp)
will always be just cell J1.

Several things need to be tweaked to make it more robust.
So question 1 is does the data to be filtered (including headers) always start in row 1?

Q 2: Since you're looking to filter the 10th column, the autofilter range needs to be that wide for certain. Is this always goingto be the case?
Q 3: Where is this code (a standard code module, a sheets'code module, perhaps the workbook's code module)? That With statement is going to need its ranges qualified.
Q 4: Using Offset(1) alone to exclude the headers is in danger of including an extra line below the filtered range. This may not matter if you're certain there's never going to be anything at all below that filtered range. It needs to be resized to one fewer rows. Will this matter?
Point 5: Once an autofilter is in place on a sheet, its range can be reliably accessed with its .Range property.

I'll provide specific code on answers to the above 4 questions, with perhaps any additional stuff yopu feel might be important to know.

[snb]

Why not posting a sample workbook ?

or using:

Sub M_snb()
    With Columns(10).SpecialCells(2)
       .AutoFilter 1, "*"
       .Offset(1).SpecialCells(12).SpecialCells(2).EntireRow.Select
    End With
End Sub

[xander1981]

Many thanks. I have now resolved this issue :

ActiveWorkbook.Sheets("PRG CFS Receipts").Select
        ActiveSheet.Range("a1").AutoFilter 10, Criteria1:="<" & GetDateRangeFrom, Operator:=xlOr, _
        Criteria2:=">" & GetDateRangeTo
        On Error Resume Next
        ActiveSheet.Range("A2:A1000").SpecialCells(12).EntireRow.Delete
         Range("A1").Select
        Selection.AutoFilter

[snb]

study the lemma 'with ... end with' in the VBA helpfiles or your VBA handbook.