PDA

View Full Version : Solved: Clearing old data from a table



MichaelL
01-09-2007, 04:01 AM
Hello

I have a small problem with a piece of code, and couldn't find anything similar when i searched the forum.

I'm trying to write a short piece of code which looks at the date in a specific cell and deletes the corresponding row of data if the date is prior to yesterday. The purpose is to clear old data from a table.

So far i have tried variations on the following two loops:

Do Until ActiveCell.FormulaR1C1 = "=TODAY()-1"
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A2").Select
Loop

Do Until Range("A2") = "=TODAY()-1"
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A2").Select
Loop

I've tried them with Do While loops as well, to no joy. Each time I step through, the loop continues indefinitely, which leads me to believe that the criteria I have set are somehow incorrect.

Any pointers would be appreciated!

Thanks

Michael

Charlize
01-09-2007, 04:52 AM
You could try this one.
Sub clear_old_data()
'no of row
Dim lrow As Long
'loop through rows
Dim start As Long
'date to check if row must be deleted
Dim vdate As Date
vdate = Date - 2
lrow = Range("A" & Rows.Count).End(xlUp).Row
'step through records starting with last row
For start = lrow To 2 Step -1
If Range("A" & start).Value < vdate Then
Range("A" & start).EntireRow.Delete
End If
Next start
End Sub
Charlize

Simon Lloyd
01-09-2007, 05:07 AM
Hi this will delete each row that has a date equal to yesterday in column A

Sub Delete_On_Date()
Dim iLastRow As Long
Dim i As Long

Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Rng As Range
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = Date - 1 Then
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub
Hope this helps,
Regards,
Simon

MichaelL
01-09-2007, 05:12 AM
Hello!

Thanks for your help Charlize and Simon! I think i've got it cracked now, but will give all the solutions a try - i've noticed a couple of things i can use to improve my effort.

I'm sure i'll be back with more problems! :)

Michael

Charlize
01-09-2007, 05:14 AM
Why are you dimming rng as range when you don't use it ?

Charlize