Results 1 to 5 of 5

Macro to delete rows prior to specific date

This is a discussion on Macro to delete rows prior to specific date within the Excel Questions forums, part of the Question Forums category; Running Excel 2007. Hi Guys, I need a macro to delete all rows before the 16/Jan/2012? Date is in Column ...

  1. #1
    Board Regular
    Join Date
    Feb 2012
    Location
    Edinburgh, UK
    Posts
    529

    Default Macro to delete rows prior to specific date

    Running Excel 2007.

    Hi Guys,

    I need a macro to delete all rows before the 16/Jan/2012? Date is in Column G and already sorted in desending order.

    Hope someone can help!

    Thanks

    Colin

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: Macro to delete rows prior to specific date

    Hi Colin & Welcome to the Board!

    perhaps:

    Code:
    Sub Del_Rows()
    With Activesheet
      .AutofilterMode = False
      .Range("A1:G1").AutoFilter
      .Range("A1:G1").Autofilter Field:=7,Criteria1:="<" & CLng(#16 Jan 2012#)
      .Autofilter.Range.Offset(1).EntireRow.Delete
      .AutofilterMode = False
    End With
    End Sub
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  3. #3
    Board Regular Matt Rogers's Avatar
    Join Date
    Sep 2011
    Location
    77550
    Posts
    799

    Default Re: Macro to delete rows prior to specific date

    Code:
    Sub DelDate()
    Dim i
    For i = Cells(Rows.Count, 7).End(xlUp).Row To 1 Step -1
    On Error Resume Next
    If Cells(i, 7).Value < "16.1.2012" Then
    Rows(i).Delete
    End If
    Next
    End Sub
    Regards Matt
    ________________________________________
    *Don't Tread On Me* [Christopher Gadsden]

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Edinburgh, UK
    Posts
    529

    Default Re: Macro to delete rows prior to specific date

    Thanks guys, both do work but Firefly's is much quicker, I have over 5000 lines and your Loop Matt takes quite long.

    Thanks again for your help

  5. #5
    Board Regular Matt Rogers's Avatar
    Join Date
    Sep 2011
    Location
    77550
    Posts
    799

    Default Re: Macro to delete rows prior to specific date

    Thanx for feedback.

    I've more time as firefly.
    Regards Matt
    ________________________________________
    *Don't Tread On Me* [Christopher Gadsden]

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com