Delete row after 120 days

[AC]

I have a date in column C, I want to delete the whole row when this date is 120 days old based on the current day,=Now(). Can this be done? Thanks I am using Excel '97
This message was edited by ac on 2002-03-22 12:19

 

[Mudface]

Yup, that's excellent .

 

[Legacy 98055]

I stand corrected!

 

[Jay Petrulis]

On 2002-03-22 15:45, C. O. Jones wrote:
Here's more efficient code (avoids using a loop) :-

Dim rng As Range
Set rng = Range([C1], [C65536].End(xlUp))
Application.ScreenUpdating = False
rng.EntireColumn.Insert
With rng.Offset(0, -1)
.FormulaR1C1 = "=IF(RC[1]+120<=NOW(),1,"""")"
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
.EntireColumn.Delete
End With

Very nice, but even more efficent:

---begin VBA---
Sub delete_rows()

Dim lastrow As Long
Dim rng As Range
Dim date_diff As Long

date_diff = Date - 120
Rows(1).Insert
Range("C1") = "temp"

With ActiveSheet
.UsedRange
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Range("C1", Cells(lastrow, "C"))
rng.AutoFilter Field:=1, Criteria1:="<=" & date_diff
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.UsedRange
End With

End Sub
---end VBA---

This has been adapted from code in Excel 2000 VBA Programmer's Reference, by John Green and Stephen Bullen.

You just don't get any better than these two, and John states that this is the most efficient way he's found. I definitely won't argue with that.

HTH,
Jay

 

[Anonymuos]

On 2002-03-22 16:56, Jay Petrulis wrote:

Very nice, but even more efficent

How much more efficient?

 

[Jay Petrulis]

On 2002-03-22 18:03, Anonymuos wrote:

On 2002-03-22 16:56, Jay Petrulis wrote:

Very nice, but even more efficent

How much more efficient?

Hi,

I did not test. Probably not much unless the dataset is quite large. I am definitely deferring to the author's expertise.

Both of these two are at the very top of the Excel community and recognized as being there (they're not famous, of course, as they are Excel programmers after all <bg> ).

That's all I can really go on here. Both of these should be lightning quick in most cases.

Stephen hasn't updated his website lately, but it is definitely worth checking out.
http://www.BMSLtd.co.uk

Regards,
Jay

 

[Ivan F Moala]

Hi Jay
Good work....Not to take away from these 2
great MVP's but the code is efficient BUT
not the fastest....just disabling screen
updating and then enabling it using identical
codes yields 6- 8% diff using 12000 rows of data. Again I'm NOT taking away from you or these 2 great MVP....


Ivan

 

[Jay Petrulis]

On 2002-03-22 18:34, Ivan F Moala wrote:
Hi Jay
Good work....Not to take away from these 2
great MVP's but the code is efficient BUT
not the fastest....just disabling screen
updating and then enabling it using identical
codes yields 6- 8% diff using 12000 rows of data. Again I'm NOT taking away from you or these 2 great MVP....


Ivan

Hi Ivan,

Not their fault. I forgot to add it. I couldn't quickly find my file with this code so I rewrote it.

John G's published code definitely has the Application.ScreenUpdating = False

If you have already set up a test environment, would you care to test the routines again? The code I "challenged" vs. the code I posted with the ScreenUpdating turned off. It would be pretty cool if C.O. Jones' code beats theirs.

Last thing -- the Application.ScreenUpdating = True is unnecessary at the end of the routine, as Excel defaults back to true at the End Sub.

Thanks for your post,
Jay

 

[Jay Petrulis]

A few test results....

Three columns of data used (A,B,C)
Code added to first line of each sub:
Test_Start = Now

Last line of code added:
Range("D1") = Now - Test_Start

Both results bomb if the entire column is filled with data
C.O Jones -- When the rng is set, the .End(xlup) reduces the range to 1 cell (C1) and the .SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete line produces an error

Jay Petrulis -- The rows(1) insert line tries to shift cells off the sheet, and throws the error.

So using only 65535 rows of data, the procedures run correctly.

The result was a dead heat. At first I formatted the result to "ss.00" but it didn't make sense to me.

The unformatted timing result runs each produced 0.0000231481462833472 repeatedly. This leads me to believe that I did not use a timer that uses the computer clocking and more sensitive/precise timers.

The following code, which tweaked C.O.'s slightly, resulted in an even faster result
0.0000115740695036948
--------------------------
Sub deleterows3()

Dim Test_Start
Dim rng As Range
Test_Start = Now

Set rng = Range([C1], [C65536].End(xlUp))
Application.ScreenUpdating = False
With rng.Offset(0, 1)
.FormulaR1C1 = "=IF(RC[-1]+120<=NOW(),1,"""")"
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
.Clear
End With
Range("D1") = Now - Test_Start

End Sub
---------------
It may be possible to make both even faster, but this really shows:

a) that both are lightning fast, and C.O.'s may be even faster with very slight modifications.

b) that I need to get a life.

Bye,
Jay

 

[Legacy 1363]

Jay

Just one more point before you start looking for a life.

I timed the macros on 8,500 rows of data.
Mine took between 6 - 7 seconds.
Yours took between 5 - 6 seconds.

The difference seems to be caused by the steps of inserting a column and then deleting it (as you have pointed out by your "tweak" of mine - so to speak.)

Since mine is longer than yours, you are herewith declared the winner by approximately 1 length.

cojones

 

[Ivan F Moala]

Last thing -- the Application.ScreenUpdating = True is unnecessary at the end of the routine, as Excel defaults back to true at the End Sub.

Thanks for your post,
Jay
[/quote]

Jay....screenupdating doesn't default back to true.

Ivan