Marco to delete rows with a future date

[markman235]

Hi everyone,

I am to write a macro for Excel 365 that will look at a date and delete the row if it is greater than today. I found one that was from a few years ago online, but it doesn't seem to work.

The date is in column F

Sub delfutureDays()
Dim cl As Long
For cl = Range("F2").End(xlDown).Row To 2 Step -1
If Cells(cl, "F") > Date Then Rows(cl).EntireRow.Delete
Next
End Sub

Any thoughts?

Mark

 

[Vogateer]

Need to reference .Value or .Value2:

Sub delfutureDays()
    Dim cl As Long
    For cl = Range("F2").End(xlDown).Row To 2 Step -1
    If Cells(cl, "F").Value2 > Date Then Rows(cl).EntireRow.Delete
        Next
    
    End Sub

 

[MARK858]

Other than the method for finding the last row doesn't account for blanks cells (the code will stop looking past first blank cell it meets) the code should work fine.

Need to reference .Value or .Value2

VBA defaults to .Value when omitted from a cell

 

[markman235]

Other than the method for finding the last row doesn't account for blanks cells (the code will stop looking past first blank cell it meets) the code should work fine.


VBA defaults to .Value when omitted from a cell

How can i ask it to ignore blank cells?

 

[MARK858]

Try (if the cells are truly blank/empty)...

For cl = Range("F" & Rows.count).End(xlUp).Row To 2 Step -1

 

[markman235]

Try (if the cells are truly blank/empty)...

For cl = Range("F" & Rows.count).End(xlUp).Row To 2 Step -1

So the good news is that it seems to work, except that it deletes everything on the sheet regardless of the date value...

Here is what I now have:

Sub delfutureDays()
Dim cl As Long
For cl = Range("F" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(cl, "F").Value2 > Date Then Rows(cl).EntireRow.Delete
Next

End Sub

 

[MARK858]

Are your dates "real" dates or are they text?

 

[markman235]

Are your dates "real" dates or are they text?

Well, that is a good question. They are set to date but the report I am exporting from (in Business Central) has the format as 2024-10-23.

 

[MARK858]

Change the format of the cells to General, do the cells still look like dates or do they change to a number?

 

[markman235]

Change the format of the cells to General, do the cells still look like dates or do they change to a number?

No, they stay the same. Here's a screenshot.