And a quick follow up on the quick follow up<g>...
I just tested the old method (iterate backwards, deleting one-line at a
time) against the original Union method I proposed in an extreme situation
that should never occur in real life and the code below (which is based on
Peter T's comments). The situation is 40,000 rows of data with every other
row slated for deletion (because the cell in Column A contains a 0); hence
there were 20,000 Areas of one row each. The code below won but "by how
much" varied depending on the contents of the worksheet. First off, one has
to recognize the extremely bizarre nature of this data set. Okay, if the
worksheet only consisted of data, no formulas, the Union method was about 3
times faster than deleting line by line (my originally proposed Union method
took so long I had to stop it manually... so thanks Peter T for noting the
problem with multiple non-contiguous areas); if there was a single column of
formulas (a simple MOD function call), the Union method dropped to only 20%
faster; and if there were 12 columns of formulas (same MOD function call),
then the Union method dropped still further to about 12% faster. Still
though, the time difference in this worst case scenario (20,000 individual
Areas) is significantly better enough to recommend the Union method over the
older one; in normally structured data, where each Area would more than
likely contain multiple cells each, I would expect the Union method would
have come in several magnitudes faster yet. Anyway, here is the code
framework I am suggesting be used to implement this hybrid code meshed
together from my initial proposal and Peter T's comments (it can be embedded
directly within a macros, modified as necessary to fit of course, or bound
into a subroutine of its own to be called from within other macros)...
Dim X As Long
Dim LastRow As Long
Dim RowsToDelete As Range
Const DataStartRow As Long = 1
Const MaxDataColumn As String = "A"
Const SheetName As String = "Sheet1"
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For X = LastRow To DataStartRow Step -1
' <<Set your test condition here>>
If .Cells(X, 1).Value = 0 Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, MaxDataColumn)
Else
Set RowsToDelete = Union(RowsToDelete, .Cells(X, MaxDataColumn))
End If
If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete xlShiftUp
Set RowsToDelete = Nothing
End If
End If
Next
End With
If Not RowsToDelete Is Nothing Then
RowsToDelete.EntireRow.Delete xlShiftUp
End If
The worksheet, starting row for the data and the column containing the
maximum rows of data (needed for calculating the LastRow of data) are
defined in the three Const statements. The actual testing is done in the
first If-Then statement inside the For-Next loop. As I have set the code
above up, the test is for a simple "does the cell in column 1 contain 0",
but, of course, this test can be made as complex as required.
Rick
Rick Rothstein (MVP - VB) said:
Just a quick follow up. In the "hide" thread (Subject: Code runs to slow),
Peter T mentioned that Union "becomes exponentially slower if/as the
number of discontinuous areas in the unioned range increase" and recommend
working in chunks of unionized groups of, say, 80 to 100 at a time... so
that is something that should probably be factored into the coding in
future postings of this method as well.
Rick