PDA

View Full Version : Delete Data if Less then Yesterday



ajames420
08-27-2007, 06:46 AM
Is there a way to Clear or delete data from a spreadsheet Columns A thru H if let's say the date is less then Yesterday? I would prefer to clear the cells. If so How would I do that?

p45cal
08-27-2007, 07:50 AM
Sure, what column's the date in?

ajames420
08-27-2007, 08:01 AM
The column the date is in is "(F4:F1625)" The date is formatted as 8/27/2007

Bob Phillips
08-27-2007, 08:21 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "F" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1
If .Cells(i, TEST_COLUMN).Value < Date - 1 Then
.Cells(i, "A").Resize(, h).ClearContents
End If
Next i

End With

End Sub

ajames420
08-27-2007, 08:36 AM
When I open up the VBA Editor where to I put that? At the top of the User Form Code or in the ThisWorkBook?

Sorry

p45cal
08-27-2007, 08:55 AM
When you open the VBE, insert a new module, one way is to:
Alt+I then M
which takes you through the menus:
Try also:Sub blah()
For Each cll In Range("F4:F1625").Cells
If cll.Value < (Date - 1) And IsDate(cll.Value) Then Cells(cll.Row, 1).Resize(, 8).ClearContents
Next
End Sub
You could also try replacing the If.. line with one ofthe folowing: If cll.Value < (Date - 1) And IsDate(cll.Value) Then Range(cll.Offset(, -5), cll.Offset(, 2)).ClearContents
If cll.Value < (Date - 1) And IsDate(cll.Value) Then Range(Cells(cll.Row, 1), Cells(cll.Row, 8)).ClearContents
They all do the same thing in this instance but one may be easier to adapt for differing scenarios

ajames420
08-27-2007, 09:19 AM
Thank you. They all work great.