Need code to delete rows based on value (VBA)

Question

What I'm trying to do is get this code to delete rows based on the date in Column B. If the date in column B falls within the last 3 days, it should get deleted. The code seems to delete 1 or 2 rows, but not all rows with 'bad' dates.

I have tried setting the formatting of all columns to General. The formatting is fine so that's not the issue. I converted the dates to numbers so I could get the last 3 days and not run into error. It should be known that this data is starting as a .csv file and is being saved as an excel file earlier in the code.

Dim myrow As Long
Dim mydate As Long

mydate = Int(CDbl(Now()))
mydate = mydate - 3

Columns("B:B").NumberFormat = "0"

Application.CutCopyMode = False

MsgBox "Last row with data:" & Cells(Rows.Count, "B").End(xlUp).row

For myrow = 1 To Cells(Rows.Count, "B").End(xlUp).row Step 1

        If Range("B" & myrow).value = mydate + 1 Or Range("B" & myrow).value = mydate + 2 Or Range("B" & myrow).value = mydate + 3 Then
        Range("B" & myrow).EntireRow.Delete
        End If

Next myrow

I expect this code to delete all rows which contain any of the last 3 days in Column B. Column B is a set of dates formatted as "43588" "43599" etc. However, it is only deleting 1 or 2 rows when the code runs. I have a message box which tells the last row with data, and it is showing correct values, meaning "Cells(Rows.Count, "B").End(xlUp).row" works fine.

I appreciate any help.

Greg Viers Greg Viers · Accepted Answer · 2019-07-15T15:47:43

When deleting rows in a loop, you must iterate through the rows in reverse order (bottom-to-top). Otherwise, when you delete a row, the rows below it are renumbered and might be skipped.

For myrow = Cells(Rows.Count, "B").End(xlUp).row to 1 Step -1

Need code to delete rows based on value (VBA)

1 Answers