+ Reply to Thread
Results 1 to 2 of 2

different times with autofilter row deletes

  1. #1
    Doug Glancy
    Guest

    different times with autofilter row deletes

    Happy new year to all of you!

    win xp, xl 2003

    I've been working on a row delete routine using autofilter. Along the way I
    found out from this group about the 8192 non-contiguous row limitation and
    so set about to break up the ranges to be filtered and deleted. While doing
    so I found that the deletions take longer if the column has more data, even
    if the filtered/deleted range is the same.

    The two macros below isolate what i'm seeing. The first puts data in column
    A of Sheet1 - alternating "a" and "b" values. This sub takes one
    parameter - the number of cells in column A to fill with data. (The workbook
    is also saved to reset the used range. Without this, the times are the
    same.) It then times the filter and delete operation - deleting the "a"
    values:

    Sub time_autofilter_delete(rows_with_data As Long)
    Dim start As Long
    Dim rows_to_delete As Range

    With Sheet1
    .Cells.Clear
    .Range("A1") = "a"
    .Range("A2") = "b"
    .Range("A1:A2").Copy Destination:= .Range("A1:A" & rows_with_data)
    .Parent.Save 'to reset used range
    start = Timer()
    .Range("A1:A1000").AutoFilter Field:=1, Criteria1:="a"
    .Range("A1:A1000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    Debug.Print "time with A1:A"; rows_with_data & " filled: " & Timer() - start
    End Sub

    In the sub below I called the sub above with 4 different amounts of data in
    column A:

    Sub compare_delete_times()
    Call time_autofilter_delete(1000)
    Call time_autofilter_delete(2000)
    Call time_autofilter_delete(10000)
    Call time_autofilter_delete(65000)
    End Sub

    Here are my results:
    time with A1:A1000 filled: 0.33203125
    time with A1:A2000 filled: 0.50390625
    time with A1:A10000 filled: 3.08203125
    time with A1:A65000 filled: 24.48046875

    I'm wondering why this is - since the same range is being filtered and
    deleted in each case - and if there's something I can do to speed it up when
    the column has lots of data.

    Thanks,

    Doug Glancy



  2. #2
    Tom Ogilvy
    Guest

    Re: different times with autofilter row deletes

    Excel has to deal with reorganizing its records for increasing numbers of
    cells would be my guess.

    --
    Regards,
    Tom Ogilvy


    "Doug Glancy" <[email protected]> wrote in message
    news:%23a7%[email protected]...
    > Happy new year to all of you!
    >
    > win xp, xl 2003
    >
    > I've been working on a row delete routine using autofilter. Along the way

    I
    > found out from this group about the 8192 non-contiguous row limitation and
    > so set about to break up the ranges to be filtered and deleted. While

    doing
    > so I found that the deletions take longer if the column has more data,

    even
    > if the filtered/deleted range is the same.
    >
    > The two macros below isolate what i'm seeing. The first puts data in

    column
    > A of Sheet1 - alternating "a" and "b" values. This sub takes one
    > parameter - the number of cells in column A to fill with data. (The

    workbook
    > is also saved to reset the used range. Without this, the times are the
    > same.) It then times the filter and delete operation - deleting the "a"
    > values:
    >
    > Sub time_autofilter_delete(rows_with_data As Long)
    > Dim start As Long
    > Dim rows_to_delete As Range
    >
    > With Sheet1
    > .Cells.Clear
    > .Range("A1") = "a"
    > .Range("A2") = "b"
    > .Range("A1:A2").Copy Destination:= .Range("A1:A" & rows_with_data)
    > .Parent.Save 'to reset used range
    > start = Timer()
    > .Range("A1:A1000").AutoFilter Field:=1, Criteria1:="a"
    > .Range("A1:A1000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    > End With
    > Debug.Print "time with A1:A"; rows_with_data & " filled: " & Timer() -

    start
    > End Sub
    >
    > In the sub below I called the sub above with 4 different amounts of data

    in
    > column A:
    >
    > Sub compare_delete_times()
    > Call time_autofilter_delete(1000)
    > Call time_autofilter_delete(2000)
    > Call time_autofilter_delete(10000)
    > Call time_autofilter_delete(65000)
    > End Sub
    >
    > Here are my results:
    > time with A1:A1000 filled: 0.33203125
    > time with A1:A2000 filled: 0.50390625
    > time with A1:A10000 filled: 3.08203125
    > time with A1:A65000 filled: 24.48046875
    >
    > I'm wondering why this is - since the same range is being filtered and
    > deleted in each case - and if there's something I can do to speed it up

    when
    > the column has lots of data.
    >
    > Thanks,
    >
    > Doug Glancy
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1