Date + 28 days delete

**bouncey** · 06-21-2011, 06:29 PM

I have dates in column G, I would like to delete the line if the date is less than (todays date +28 days). I also need to do this only if column J has "reschedule out" in the cell.

All help appreciated

**p24leclerc** · 06-21-2011, 06:47 PM

try this: I assumed there was always something in each cell of column A of your table. If not, change A1 and 1 in this line: For Each c_ell In Range("a1", Cells(20000, 1).End(xlUp))

Like if you have column C filled, the line would be:
For Each c_ell In Range("C1", Cells(20000, 3).End(xlUp))

Public Sub delete_rows()
Dim c_ell

For Each c_ell In Range("a1", Cells(20000, 1).End(xlUp))
    If c_ell.Offset(0, 6) < (Now() + 28) Then
        If c_ell.Offset(0, 9) = "reschedule out" Then
            c_ell.EntireRow.Delete
        End If
    End If
Next

End Sub

**stnkynts** · 06-21-2011, 06:58 PM

Option Explicit

Sub Remove28DaysAgo()
Dim lastrow As Long, icell As Long

lastrow = Range("G" & Rows.Count).End(xlUp).Row

For icell = lastrow To 1 Step -1
    If Range("J" & icell).Value = "reschedule out" Then
        If Range("G" & icell).Value < Date - 28 Then
            Range("G" & icell).EntireRow.Delete Shift:=xlUp
        End If
    End If
Next icell

End Sub

**bouncey** · 06-22-2011, 02:42 PM

I cannot get either of these solutions to work!

**watersev** · 06-22-2011, 03:23 PM

hi, bouncey, can you post sample workbook?

**bouncey** · 06-22-2011, 04:00 PM

Hi Watersev,

Please see attached wb for a before and after. Any help achieving the after would be most appreciated as it takes me about an hour a day do sort. Automating this process would be a god send.

Thanks

**watersev** · 06-22-2011, 04:24 PM

please check attachment, run code "test", all the rows with dates less than 20.07.2011 (Today+28 days) and with value "Reschedule out" are deleted

**bouncey** · 06-22-2011, 04:41 PM

Perfect, thank you.

If you could help with anything else in the before and after wb's I would be most grateful.

**watersev** · 06-22-2011, 04:50 PM

what else do you need?

**bouncey** · 06-22-2011, 05:01 PM

I would like to automate the summary sheet!

Thanks

**watersev** · 06-22-2011, 05:23 PM

I would reply tomorrow if you do not mind and no one does it before me

**watersev** · 06-23-2011, 03:59 PM

will you accept summary with this layout?

**bouncey** · 06-23-2011, 04:06 PM

yes, that would be good.

Date + 28 days delete

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Re: Date + 28 days delete

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions