View original (French)

VBA delete row if date<today

Solved
tchernosplif Posted messages 714 Status Member -  
tchernosplif Posted messages 714 Status Member -
Hello,

Could you help me write a macro that would delete a row(s) if the date in column D is earlier than today?

(each row will have a date in column D)

Thank you in advance for your invaluable help.

Configuration: Windows Vista / Internet Explorer 7.0

3 answers

Answer 1 / 3
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
 
Hello, 

Sub suppLig() Dim lig As Long For lig = [D65536].End(xlUp).Row To 1 Step -1 If IsDate(Cells(lig, 4)) And Cells(lig, 4) < Date Then Rows(lig).Delete Next lig End Sub


Eric

PS: when you delete a line, always loop from the bottom, otherwise each line following a deleted line is not tested.
3
tchernosplif Posted messages 714 Status Member 249
 
Hello,

Thank you very much eriiic.
0
Answer 2 / 3
tchernosplif Posted messages 714 Status Member 249
 
I didn't specify that there could be empty lines and the only way I found to avoid getting stuck on an empty line is to call the "Test" macro several times in a row. It's a bit of a hack, but it works. (I'm open to a more elegant solution)

Sub Test()
For i = 1 To 100
If Range("A" & i).Value < Now And Range("A" & i).Value <> "" Then
Rows(i).Delete
Else
End If
Next i

End Sub

Private Sub CommandButton1_Click()
Test
Test
Test
Test
End Sub

Thank you for your help
0
Answer 3 / 3
ed555 Posted messages 61 Registration date   Status Member Last intervention  
 
Hello,

the code below should work (to be adapted to your specific case):

Dim la_date As Date
la_date = Sheets(1).Range("A1").Value

If la_date < Now Then
Sheets(1).Range("A2").Value = 1
End If
-2
ed555 Posted messages 61 Registration date   Status Member Last intervention  
 
to delete:

Sheets(1).Rows(2).Delete
-1