Delete row based on date

greg1075

Board Regular
Joined
Oct 7, 2011
Messages
98
Hi,

I am have a workbook with 3 sheets. Each sheet contains dates in column G (starting at G14). I need all three sheets to delete the related row for all dates older than today. Each sheet as well as the entire workbook are also password protected so I need the code to deactivate/reactivate the protection. I have looked in multiple forums but none of the code I found has worked for me.

Thanks!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Try

Code:
Sub atest()
Dim LR As Long, i As Long, ws As Worksheet
ActiveWorkbook.Unprotect
For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Unprotect
        LR = .Range("G" & Rows.Count).End(xlUp).Row
        For i = LR To 14 Step -1
            If .Range("G" & i).Value < Date Then .Rows(i).Delete
        Next i
        .Protect
    End With
Next ws
ActiveWorkbook.Protect
End Sub
 
Upvote 0
Thanks VoG.

I must be doing something wrong because the rows are not getting deleted after I enter the code in the workbook and save... I am not sure if this impact anything in where the code should go, but the workbook already has come code added (to open directly in full screen and to not prompt users to save when exiting Excel). The code already present is:

Code:
Private Sub Workbook_Activate()
On Error Resume Next
With Application
.DisplayFullScreen = True
.CommandBars("Worksheet Menu Bar").Enabled = False
End With
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
With Application
.DisplayFullScreen = False
.CommandBars("Worksheet Menu Bar").Enabled = True
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

I added your code at the end of the code above.
 
Upvote 0
It has to go in a standard module. Press Alt +F11, Insert > Module then paste in the code. Press ALT + Q.

Press ALT + F8, double click atest.
 
Upvote 0
Great! Just had to replace "activeworkbook" with "activesheet" and add the password in the code but it now works like a charm. Thanks VoG! Much appreciated!
 
Upvote 0

Forum statistics

Threads
1,223,431
Messages
6,172,073
Members
452,444
Latest member
ShaImran193

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top