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.

 

[Michael M]

I'm confused.....How is it working if the dates in Sheet2 don't fit the criteria...

 

[MARK858]

Based on what Michael has stated and post number 21

Anything that is greater than 7/8/19 and less than 3/10/19 on sheet two

If that is 9th July 2019 and 11th March 2019 (remember a day has passed) then you need to change my code in post number 28 from

 .AutoFilter Field:=8, Criteria1:= _
                    ">=" & CLng(Date + 70), Operator:=xlAnd, Criteria2:="<=" & CLng(Date + 190)

to

 .AutoFilter Field:=8, Criteria1:= _
                    "[COLOR="#FF0000"]<[/COLOR]=" & CLng(Date + 70), Operator:=xl[COLOR="#FF0000"]Or[/COLOR], Criteria2:="[COLOR="#FF0000"]>[/COLOR]=" & CLng(Date + 190)

Edit:

I know that is it ugly, but it is working.

I'm confused.....How is it working if the dates in Sheet2 don't fit the criteria...

 

[Roodoc]

I used record macro to copy and paste the data from sheet1 to sheet2.

Once there I used the coding provided to cut out the dates before 70 days, and after 190.

Again I used record macro to remove the duplicates, as there will already be data present in the sheet.

Sub KeepBetween2and6months()

'Unhides columns
    Cells.Select
    Range("B1").Activate
    Selection.EntireColumn.Hidden = False
'copies sheet1 Data
    Sheets("Sheet1").Select
    Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
'Pastes data
    Sheets("Sheet2").Select
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Range("A2").Select
    Application.CutCopyMode = False
'puts dates in chronological order
    Range("H3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("H3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A3:O62")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'deletes less than 2 months and more than 6 months
'thanks to reddit - Mark858
        Application.ScreenUpdating = False
    On Error Resume Next
    Sheets("Sheet2").ShowAllData
    On Error GoTo 0
    With Sheets("Sheet2").Range("A2:O" & Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=8, Criteria1:= _
                    "<=" & CLng(Date + 70), Operator:=xlOr, Criteria2:=">=" & CLng(Date + 190)

        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
        Sheets("Sheet2").ShowAllData
    End With
'Removes duplicates
    Application.ScreenUpdating = True
        Range("A3:H70").Select
    ActiveSheet.Range("$A$2:$H$70").RemoveDuplicates Columns:=Array(1, 2, 3, 8), _
        Header:=xlYes
        
End Sub