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

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" wrote in message
...
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