Delete Data if Less then Yesterday

[ajames420]

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]

Sure, what column's the date in?

[ajames420]

The column the date is in is "(F4:F1625)" The date is formatted as 8/27/2007

[Bob Phillips]

[vba]

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
[/vba]

[ajames420]

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]

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:[vba]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
[/vba]You could also try replacing the If.. line with one ofthe folowing: [vba]If cll.Value < (Date - 1) And IsDate(cll.Value) Then Range(cll.Offset(, -5), cll.Offset(, 2)).ClearContents
[/vba][vba]If cll.Value < (Date - 1) And IsDate(cll.Value) Then Range(Cells(cll.Row, 1), Cells(cll.Row, 8)).ClearContents
[/vba]They all do the same thing in this instance but one may be easier to adapt for differing scenarios

[ajames420]

Thank you. They all work great.