Delete all rows except between date + 70 and date + 190

[Roodoc]

I have been working on this for two days to no avail. I am extremely new to VBA.

In my macro I would like to delete the rows that have due dates more than 190 days away from today's date, and those with due dates that are within 70 days.

Sub KeepBetween70and190()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("sheet2")

Application.ScreenUpdating = False

LastRow = Cells(Rows.Count, 8).End(xlUp).Row

    For i = LastRow To 2 Step -1
    
        If ws.Cells(i, 8).Value >= Date + 191 Or ws.Cells(H, 8).Value < Date + 70 Then
            .Rows(i).Delete
        End If
    
    Next i
    
Application.ScreenUpdating = True
    

End Sub

.Rows(i).delete is highlighted with the error "Compile error: Invalid or unqualified reference"

I can not figure this out for the life of me. If you know what I am doing wrong could you point out what and why it is wrong so that I can learn from this, please.

 

[Roodoc]

This code still gives me delete method of Range class failed.

I have 60 rows of data in sheet1, plus the header equals 64.

I am trying to copy and paste the table from sheet1 to sheet2 and keep the due dates (column h) that are between 2 and 6 months from today.

On sheet3 I would like to have the due dates that are within 70 days.

Sheets 2 and 3 would have more rows of data entered in extra columns. Updating these sheets should not delete the data until the date is less than 70 days on sheet2 and past today's date on sheet3.

I am getting stumped at every turn. Sorry if this is confusing, my brain is turning to mush.

 

[MARK858]

Upload a copy of your workbook to a free file hosting site like www.box.com or www.dropbox.com (all sensitive data sanitized), mark it for sharing then copy/paste the link it produces in the thread.

 

[Mentor82]

Sorry, Mark858 is right- I've forgotten to declare i. - here's the code fixed

Sub KeepBetween70and190()
Dim ws As Worksheet
Dim LastRow&, i&
Const COL% = 8 'default column
Const MROW% = 2 'main/header row
Const WITHIN_DAYS& = 70 'constant value for 2nd argument
Const OVER_DAYS& = 191 'constant value for 1st argument
Const sht$ = "sheet2" 'constant for the sheet name
On Error GoTo ErrorHandler
Set ws = ThisWorkbook.Sheets(sht)
Application.ScreenUpdating = False
With ws
LastRow = .Cells(Rows.Count, COL).End(xlUp).Row

For i = LastRow To MROW Step -1
If .Cells(i, COL).Value >= Date + OVER_DAYS Or .Cells(i, COL).Value < Date + WITHIN_DAYS Then
.Rows(i).Delete
End If
Next i
End With
Application.ScreenUpdating = True
Set ws = Nothing
MsgBox "Done"

Exit Sub
ErrorHandler:
Application.ScreenUpdating = True
Set ws = Nothing
MsgBox "Unexpected error occured." & vbNewLine & "Error no: " & Err.Number & vbNewLine & "Error description: " & Err.Description

End Sub

 

[Michael M]

Are you trying to delete rows out of a Table ??, not just a simple range !

 

[Roodoc]

https://www.dropbox.com/s/8rnhxit1d38upw5/Attempt_1.xlsm?dl=0

Beware, I'm extremely new and it may mush your brain like it has mine.

 

[MARK858]

In your other post your dates were in mm/dd/yyyy but your dates in the sheet that I have downloaded are in dd/mm/yyyy.

What is your Windows regional date setup?
In an empty cell what does =ISNUMBER(H8) give you?

 

[Roodoc]

True

 

[Michael M]

I might be reading this wrong, but if your are deleting rows from Sheet2, which you say is your set ws,ALL of the dates fall outside the requested criteria, so the rows won't be deleted ??
But if it's sheet1 you are trying to remove there will be some removed !!

 

[Roodoc]

I am using mm/dd/yyyy

 

[MARK858]

You have no dates in Sheet2 that are less than 10/03/2019 or greater than 8/7/2019 so what do you expect to be deleted?