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
Bookmarks