Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with VB code to delete date rows (2003)

    I am writing a macro and do not know the VB code to delete rows in a spreadsheet which are just a week old from todays date and delete other rows.
    i.e. only keep rows which have a date in the Date Completed column that are 1 week from today or less.
    See screenshot
    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Help with VB code to delete date rows (2003)

    hi jj,

    You haven't given the range to work with, so the following macro has been coded to solicit the target column and the top data row:
    <code>Sub CleanUp()
    Dim iRow As Long
    Dim TopRow As Long
    Dim ColSelect As String
    ColSelect = InputBox("What Column do you want to test?", "Test Column", "A")
    TopRow = InputBox("What Row do you want to start at?", "Start Row", "1")
    With ActiveSheet
    For iRow = .Cells.SpecialCells(xlCellTypeLastCell).Row To TopRow Step -1
    If IsDate(.Range(ColSelect & iRow).Value) Then
    If .Range(ColSelect & iRow).Value + 7 < Date Then .Rows(iRow).EntireRow.Delete
    End If
    Next iRow
    End With
    End Sub</code>
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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