Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Delete row after 120 days

  1. #11
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yup, that's excellent .

  2. #12
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I stand corrected!

  3. #13
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #14
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Very nice, but even more efficent

    [quote]


    How much more efficient?

  5. #15
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [quote]
    On 2002-03-22 18:03, Anonymuos wrote:
    [quote]
    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 ).

    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

  6. #16
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #17
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #18
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #19

    Join Date
    Mar 2002
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #20
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •