Results 1 to 6 of 6

Thread: Delete Filtered Rows Excluding The Header

  1. #1

    Delete Filtered Rows Excluding The Header

    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?
    Code:
    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

  2. #2
    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?

  3. #3
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    94
    Rep Power
    12
    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.

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Why not posting a sample workbook ?

    or using:

    Code:
    Sub M_snb()
        With Columns(10).SpecialCells(2)
           .AutoFilter 1, "*"
           .Offset(1).SpecialCells(12).SpecialCells(2).EntireRow.Select
        End With
    End Sub
    Last edited by snb; 03-20-2014 at 08:12 PM.

  5. #5
    Many thanks. I have now resolved this issue :
    Code:
    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

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    study the lemma 'with ... end with' in the VBA helpfiles or your VBA handbook.

Similar Threads

  1. Delete Rows Based on Conditions
    By AbiG2009 in forum Excel Help
    Replies: 6
    Last Post: 12-26-2018, 01:24 PM
  2. Replies: 6
    Last Post: 03-26-2014, 03:04 PM
  3. Delete blank rows
    By dhivya.enjoy in forum Excel Help
    Replies: 5
    Last Post: 10-23-2013, 10:04 PM
  4. Delete Rows
    By ayazgreat in forum Excel Help
    Replies: 2
    Last Post: 12-13-2012, 11:48 AM
  5. Delete Empty Rows
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-28-2011, 02:13 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •