Deleting rows from archived sheet after 730 have passed

nakiasl

New Member
Joined
Feb 12, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
I have the following code to delete old data from my archived sheets after 730 days have passed, but it is not working. When I use the button nothing happens. I have attached a screen shot of my workbook. I need the button to delete rows, starting at row 4 and moving down the worksheet for an infinite amount of rows, that have a roll off date greater then 730 days. Any help will be greatly appreciated.

Sub DeleteCells()
Dim lr As Long, I As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lr = Range("D" & Rows.Count).End(xlUp).Row
For I = lr To 1 Step -4
If Range("A" & I).Value <= Date - 730 Then Rows(I).Delete
Next I
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 

Attachments

  • Example Workbook 2.png
    Example Workbook 2.png
    28.2 KB · Views: 6

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Why are you using Step -4looking at your image it should be -1
Also are you trying to check the dates in col A or col D?
 

nakiasl

New Member
Joined
Feb 12, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
when it is set to -1 it was deleting everything in row one, which is merged with row two. so it would delete the data that wanted but also delete the heading and the button that I have in rows one and two. I would like to use the dates in column D. if the date in column d is greater then or equal to 730 days, i want it to be deleted when i use the button that i have labeled clean up.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
For I = lr To 4 Step -1
   If Range("D" & I).Value <= Date - 730 Then Rows(I).Delete
Next I
 

nakiasl

New Member
Joined
Feb 12, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thank you so much that worked perfectly!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

morkar

New Member
Joined
Mar 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello! Is there a way to delete the rows with older dates but leave any rows with blank dates?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Please start a thread of your own for this question. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,486
Messages
5,636,614
Members
416,929
Latest member
Nitil

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
Top