VBA to delete rows if dates in a column match for the whole week

[asad]

Hello All,

I am trying to make a macro work for me. I found a code on a website and tried to modify it to suit my need. The modified code is as follows:

Sub delrow()Dim row As Long
Dim lastrow As Long
Dim mindate As Date
Dim maxdate As Date


mindate = CDate(InputBox("Please enter start date in dd/mm/yyyy format", vbOKOnly))


maxdate = CDate(InputBox("Please enter last date in dd/mm/yyyy format", vbOKOnly))


lastrow = Cells(Rows.Count, "D").End(xlUp).row
row = 1
Do While row <= lastrow
[COLOR=#ff0000]transdate = CDate(Cells(row, 4)[/COLOR])
    If transdate < mindate Or transdate > maxdate Then
        Rows(row).EntireRow.Delete
    Else
        row = row + 1
    End If
Loop
End Sub

But when I try to run it, the row with red fonts gets highlighted and code stops.
All I am trying to do is to get user to either input a date range for the whole week, or if you guys think it better for the user to enter only start date for the week, then I am happy with that too. But after getting the date, I want to check in column D for all the rows that have any date within that week and delete the whole rows.
Your help will be greatly appreciated.

Thanks
Asad

 

[asad]

I have changed the code a bit. Now it is going into endless loop

Sub delrow()Dim row As Long
Dim lastrow As Long
Dim mindate As Date
Dim maxdate As Date


mindate = InputBox("Please enter start date in dd/mm/yyyy format", vbOKOnly)


maxdate = InputBox("Please enter last date in dd/mm/yyyy format", vbOKOnly)


lastrow = Cells(Rows.Count, "D").End(xlUp).row
row = 1
Do While row <= lastrow
transdate = Cells(row, 4)
    If transdate > mindate Or transdate < maxdate Then
        Rows(row).EntireRow.Delete
    Else
        row = row + 1
    End If
Loop
End Sub

 

[asad]

Thanks for looking at it. My brother fixed it for me

Here is the code if someone wants.

Sub delrow()Dim row As Long
Dim lastrow As Long
Dim mindate As Date
Dim maxdate As Date


mindate = InputBox("Please enter start date in dd/mm/yyyy format", vbOKOnly)


maxdate = InputBox("Please enter last date in dd/mm/yyyy format", vbOKOnly)


lastrow = Cells(Rows.Count, "D").End(xlUp).row
row = 2
Do While row <= lastrow
transdate = Cells(row, 4)
    If transdate > mindate Or transdate < maxdate Then
        Rows(row).EntireRow.Delete
    End If
    row = row + 1
Loop
End Sub