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.

 

[Fluff]

You haven't put ws in front of that line.
You've also missed it from the line where you are finding LastRow

 

[Michael M]

2 errors
you need top remove the dot before the .Rows(i).delete

AND

ws.Cells(H, 8) should be ws.Cells(i, 8)

 

[My Aswer Is This]

Almost the same question you asked here:
https://www.mrexcel.com/forum/excel...ows-sheet1-sheet2-based-date.html#post5199230

 

[Roodoc]

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

Application.ScreenUpdating = False

LastRow = ws.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(i, 8).Value < Date + 70 Then
            Rows(i).Delete
        End If
    
    Next i
    
Application.ScreenUpdating = True
    

End Sub

I am now getting "Delete method of Range class failed"

I've tried it with and without the ws.before Rows(i).Delete

 

[Michael M]

Is the sheet protected ??

 

[Roodoc]

No, it is not.

 

[MARK858]

ws should be in front of Rows as Fluff stated, so you are saying the code below gives that error? if yes what is the value of i when it errors?
Edit: Do you have any merged cells?

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

Application.ScreenUpdating = False

LastRow = ws.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(i, 8).Value < Date + 70 Then
            ws.Rows(i).Delete
        End If
    
    Next i
    
Application.ScreenUpdating = True
    

End Sub

Btw, adapting the filtering you were trying to do in your other post is more efficient than looping.

 

[Mentor82]

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.

Hi,
I have fixed your error. I assume that the worksheet "sheet2" you refer might not be the one which is active, so for these reason when you refer to the particular worksheet you should aways refer to it in either each line (cells, range etc) or using "with …. end with" statement. Next important thing is that you should always declare variables and constants. Also when you run the application.screenupdating you should always take care of turning it on in case of error. Heres's below the code I've modified a little bit for you. You have there all decalred variables and constants so you can play around with them if necessary without changing the particular code itself.

Sub KeepBetween70and190()
Dim ws As Worksheet
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

 

[MARK858]

. You have there all decalred variables

Really? where have you declared i as Long or Integer?