Peter
Tue Mar 18 12:45:57 CDT 2008
"Rick Rothstein (MVP - VB)" wrote in message
Comments in line -
> A couple of questions about your suggested approach...
>
> 1. Is the time penalty for marking rows and then sorting all the data
> (especially if there are formulas involved) really less than accumulating
> and deleting whole Areas as they are iterated?
I particularly qualified with "Would work well for large data sets without
formulas". To which perhaps I should have added "where potentially a large
qty of discontiguous rows may require deletion". In such a scenario I would
expect the method to be very significantly faster. FWIW sheets with large
volumes of data should not contain formulas.
Time for just about any checking of criteria and filling an array to "mark"
rows to be deleted is likely to be insignificant in comparison with the
process of actually deleting the rows. At the end of the my post I also
suggested, if the count of rows to be deleted is relatively small it would
be better to delete using say your union method or even individually, ie
abort the insertion of the helper column. However if the count is large
'sort rows into a single area t.b. deleted' should work well..
> 2. Instead of always inserting a helper column, wouldn't it be more
> efficient to check if all the columns are in use and, if not, use the
> existing empty column to the right of the ending UsedRange column (saving
> the insert option only if in the rare case every column is in use)?
Absolutely, and I also suggested same - "probably faster to use a spare
empty column for the helper". However I would use the column just to right
of last column that actually contains any data which is not necessarily the
same as the last UR column. The method of course could not be used at all if
there are no spare columns, would not be possible to insert a column.
It just one more approach, good for some scenarios but not the best for all.
Regards,
Peter T
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%23Uwp9PHiIHA.5280@TK2MSFTNGP02.phx.gbl...
> > Hello again Rick, you seem to be getting into this larky !
> >
> > Here's yet another approach, write a helper column to be sorted into
rows
> > tb
> > deleted. Would work well for large data sets without formulas.
> >
> > A typical reason for deleting rows is to remove duplicates. So instead
of
> > looking for zeros, as in the contrived example, basically the same
method
> > could be used whereby cells in a helper column are "marked" for
deletion.
> > "Marked" cells are sorted into a single block of rows at the bottom for
> > subsequent deletion. Instead of 'inserting' a helper column as in the
> > demo,
> > probably faster to use a spare empty column for the helper.
> >
> > I think this approach should be significantly faster than either the
union
> > (in chunks) or filter methods with large numbers of discontiguous rows
to
> > delete, though either of the other methods might be faster depending on
> > the
> > scenario. As posted the demo loops 10k cells finding 5k rows tb deleted,
> > but
> > 40/20k only about twice as long (not linear due to insertion of helper
> > column).
> >
> > Excuses in advance, cobbled together in haste with possibility of
errors!
> >
> > Sub DelRows_SortMethod()
> > Dim i As Long, nLastRow As Long, nRow As Long
> > Dim vArr1
> > Dim rng As Range
> > Dim t As Single
> >
> > Set rng = Range("A10000")
> > rng.Columns(1).Clear
> >
> > Range("A1") = "headerA"
> >
> >
> > ReDim arr(1 To rng.Rows.Count, 1 To 1) As Long
> > For i = 1 To UBound(arr) Step 2
> > arr(i, 1) = 1
> > Next
> >
> > rng.Value = arr
> > Erase arr
> >
> > Stop ' see the column of 0's & 1's
> > ' rows of 0's tb deleted
> >
> > ' above just to set sample data
> > '''''''''''''''''
> > ' real stuff starts here
> >
> > t = Timer
> >
> > Set rng = Range("A2") ' the top cell interest
> >
> > nLastRow = Cells(Rows.Count, rng.Columns(1).Column).End(xlUp).Row
> > Set rng = Range(rng, Cells(nLastRow, rng.Column))
> >
> > vArr1 = rng.Value
> >
> > ReDim vArr2(1 To UBound(vArr1), 1 To 1)
> >
> >
> > ' "mark" rows tb deleted with a high number (eg the row.count) and
> > other
> > ' rows to keep with consecutive nos to maintain original order
> > ' populate the array with markers (the high no.) & consec' nos.
> > For i = 1 To UBound(vArr1)
> > If vArr1(i, 1) = 0 Then
> > vArr2(i, 1) = UBound(vArr1) + 1
> > Else
> > nRow = nRow + 1
> > vArr2(i, 1) = nRow
> > End If
> > Next
> >
> > If nRow < rng.Rows.Count Then
> >
> > Columns(rng(1, 2).Column).Insert ' helper column
> >
> > rng.Offset(, 1).Value = vArr2 ' dump marker's
> >
> > ' sort to put markers at the bottom, ie rows tb deleted
> > rng.EntireRow.Sort Key1:=rng(1, 2), _
> > Order1:=xlAscending, _
> > Header:=xlNo, _
> > OrderCustom:=1, _
> > MatchCase:=False, _
> > Orientation:=xlTopToBottom
> >
> > ' reference the marked rows and delete
> > rng.Offset(nRow).Resize(rng.Rows.Count - nRow).EntireRow.Delete
> >
> > ' delete the helper column
> > rng(1, 2).EntireColumn.Delete
> > Else
> > ' nothing to do
> >
> > End If
> >
> > Debug.Print Timer - t
> > End Sub
> >
> > This demo inserts the helper column and sorts even if there is only one
or
> > a
> > small number of rows to delete. In practice it would be better to
> > incorporate something like this -
> >
> > If nRow < rng.Rows.Count Then
> > If (rng.Rows.Count - nRow) < [say] 200 Then '
> > Loop vArr2 from the top down deleting the "marked" rows with
v.small
> > qty
> > or why not use Union method reading the "marked" array
> >
> > Else
> > do as in the demo above
> >
> >
> > Er, hope that all makes sense....
> >
> > Regards,
> > Peter T
> >
> >
> >
>