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]

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

 

[MARK858]

Then you asked the wrong question. 7/8/19 is 220 days from today, 3/10/2019 is 277 days from today (and you still have nothing that matches those criteria on Sheet2 so nothing will be deleted).

Also as Michael suspected you are using a Table not a range so the syntax is different.

 

[My Aswer Is This]

Your original post said:

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.

I would think you should say Prior to or After.

What does away mean?
​

 

[Michael M]

@MARK858
I think the 3/10/19 is actually 10th March 2019......90 days from today

But you point is still correct, as I mentioned earlier, in the thread...

 

[MARK858]

Michael, on the sheet I downloaded the dates are in dd/mm/yyyy (although it might have converted to my settings) which is why I asked what the Regional date settings the OP is using are (and never got answered so who knows ).

Anyway I am not writing code for a ListTable today as I don't like them at the best of times so I will leave to you/others

 

[Roodoc]

I didn't know that with it being a table made it more difficult. I've converted them back to range instead of tables.

 

[MARK858]

So does that mean that all the rows in Sheet2 of your sample sheet would be deleted as they are all later than the 31st March and earlier than 9th July (yes I have gone past midnight here) if Michael is correct?

Also if you have now changed it to a range do you still have filters in place?

 

[MARK858]

As I am getting some sleep now, first of all make sure when you test that you have some data in column H that doesn't fall into the criteria then try....

Sub Filterit3()
    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:=xlAnd, 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
    Application.ScreenUpdating = True
End Sub

 

[Michael M]

@Roodoc
Did you check your date criteria against the values on Sheet2.
According to your request Nothing would be deleted !

 

[Roodoc]

So far this is what I am using that has been working.

Sub Setup180Sheet()

    Cells.Select
    'Unhides the columns
    Range("B1").Activate
    Selection.EntireColumn.Hidden = False
    'Copies the data from sheet1
    Sheets("Sheet1").Select
    Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    'pastes data to sheet2
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    'remove duplicates
    Range("A2").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$O$62").RemoveDuplicates Columns:=Array(1, 2, 3, 8), _
        Header:=xlYes
    'places data in chronological order
    Range("H2:H61").Select
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("H2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:O61")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Dim ws As Worksheet
    '
    '
    'deletes data that should be in 60day process and
    'data that is more than 191 days away
    '
Dim LastRow&
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

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