Faster Way to Delete Rows?

[PoggiPJ]

My spreadsheet is a contract file that includes a list of part numbers being sold. Each part number refers to a lookup table of ALL about 20,000 valid part numbers and prices. Once the contract is finalized, I want to make the contract sheet smaller by deleting all rows in the lookup table that are not required for this contract.

The following code works, but it takes more than 10 minutes to work through the complete list. I read down the lookup table. For each record in the lookup table, I call a routine that reads through an array of the part numbers that are included in this contract. If the lookup table part number IS included in the contract, I skip it. If it is NOT required, I delete it. I then return to the main lookup table and read in the next lookup table record.

This is the main routine where I progress down the big lookup table.

'Work down the Price File range from top to bottom
    Set RefTableRange = DSWPrices.Range("DSWPriceRange")
    RefTableIndex = 1
    Application.Calculation = xlCalculationManual
    While RefTableIndex < RefTableRange.Rows.Count
        RefTableIndex = RefTableIndex + 1
        'check if this part number is included in the contract
        Call CheckRefTableRow(RefTableRange, RefTableIndex)
    Wend

This is the routine that checks to see if the part is included in the contract.

Private Sub CheckRefTableRow(ByRef RefTableRange As Range, ByRef RefTableRow As Long)
Dim ThisPartIsNotInTheContract As Boolean
Dim x As Long
    
    'assumption that this part will NOT be in the contract and will delete it.
    ThisPartIsNotInTheContract = True
    While ThisPartIsNotInTheContract
        For x = 1 To maxPAParts
            If RefTableRange.Cells(RefTableRow, 1) = PAPartArray(x) Then
                'the part actually IS in the contract
                ThisPartIsNotInTheContract = False
                Exit For
            End If
        Next x
        If ThisPartIsNotInTheContract Then
            'Since this part isn't included in the contract, delete it.
            RefTableRange.Cells(RefTableRow, 1).EntireRow.Delete
            deletedRecordCount = deletedRecordCount + 1
        End If
    Wend
End Sub

I'm wondering if there is a better approach - or if there is a way to select the individual rows to be deleted, and then delete them all at once. Any ideas?

 

[Legacy 14611]

Vladimir (and others interested)

Have just had a look at your last posted code. It was good. Gave correct results on test data, fast, and didn’t modify/distort the original data, apart from row deletion as requested by OP.

Yes, seems about 22% faster than my posted code. Given the title of this thread, I guess your code takes the prize so far.

Good work!

 

[NdNoviceHlp]

This certainly has been an interesting read. I just thought that I would add that removing a row is just the same as bringing all the rows below it up 1 row. Here's some code that takes that approach. It only deletes the column range specified which seems nb. to this thread. It's probably fairly fast and not quite so coded. HTH. Dave

Public Function RemoveRow(RowToRemove As Integer)
Dim Letter As Variant, Lastrow As Long, Cnt As Long, i As Long
'removes rows between specified contiguos columns
'set contiguos columns within function
'eg. use Call RemoveRow(3) to remove 3rd row of cols specified

'set columns to be deleted ie. "A" to "X" in this eg.
Letter = [COLUMN(A:X)]
'set total rows ie. uses "A" in this eg.
With Sheets("Sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

'remove row by moving all rows below RowToRemove up 1 row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Cnt = RowToRemove To Lastrow
For i = LBound(Letter) To UBound(Letter)
Sheets("Sheet1").Cells(Cnt, Letter(i)) = _
   Sheets("Sheet1").Cells(Cnt + 1, Letter(i))
Next i
Next Cnt
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Function

 

[Boller]

Vladimir (and others interested)

Count me in.

(I guess it's difficult to improve on VZI's method. )

 

[ZVI]

...
Good work!

Thanks Rugila, but in my testings with some other data your code can be faster than mine.

Both methods are improvable with early binding for dictionary, and may be with API copy memory method. But surely there should be common sense in it ( L: desipere in loco ), or at least the presence of the issue - milliseconds in performance time in many cases seems isn’t the problem at all.

I agree with Boller that there is no the absolutely fastest method because it depends from many factors.
And there is no winner in this thread - rather all we are.

Cheers,
Vladimir

 

[Boller]

ZVI

desipere in loco

You see - you are not unfamiliar with Latin.

And here's a useful link for anyone who thinks that quoting Latin phrases might impress someone :-

http://www.yuni.com/library/latin.html

This link, however, is a bit suspect (exempli gratia : escariorium lavator).

 

[Legacy 14611]

So the thread didn’t quite RIP after my post.

OK, my comments referred specifically to results from that test data I provided (initially posted for a rather different purpose). And yes, different test data can give different and opposite conclusions, as did (rather surprisingly to me) using a different computer.

Addressing the main topic of this thread, methods of fast deletion of rows depend on the nature of the datasets, where it may be quicker either to retain the rows one wants, as did my earlier code, or delete the ones one doesn’t, as do ZVI’s codes and the one at the end of this post.

Deletion seems most quickly done in blocks, so codes relying on a row-by-row deletion (such as that by NdNoviceHlp can be a priori presumed to be slow in large datasets unless clear evidence to the contrary is provided. Likewise, my experience is that Excel formulas, and advanced filter and stuff, don’t work that well on large datasets, although again I’d be happy to see clear evidence to the contrary.

This thread, including some of its sideshow amusements, has been most entertaining and instructive. Thanks to all participants.

Sub delrowsxx()
Dim t As Single: t = Timer
Dim z As Object, e, m As Integer
Dim b, i As Long, p As Long
Application.ScreenUpdating = 0
Set z = CreateObject("Scripting.Dictionary")
For Each e In Sheets("Sheet2").Cells(1).CurrentRegion.Resize(, 1).Value
    z.Item(e) = 0
Next
With Sheets("Sheet1").Cells(1).CurrentRegion
m = .Columns.Count
b = .Resize(, 1).Offset(, m)
   For Each e In .Resize(, 1).Value
        i = i + 1
        If z.exists(e) Then
            p = p + 1
            b(i, 1) = 666
        End If
    Next
.Resize(, 1).Offset(, m) = b
.Resize(, m + 1).Sort .Cells(1).Offset(, m), 1, header:=xlNo
.Resize(.Rows.Count - p).Offset(p).ClearContents
.Resize(, 1).Offset(, m).ClearContents
End With
Application.ScreenUpdating = 1
MsgBox Format(Timer - t, "0.000") & " secs"
End Sub

 

[Boller]

Likewise, my experience is that Excel formulas, and advanced filter and stuff, don’t work that well on large datasets, although again I’d be happy to see clear evidence to the contrary.

These methods tend to be slow on large datasets normally because of a large number of non-contiguous areas to be deleted (even although there may be only one line of code).

Provided that using formula(s) in a helper column is not in itself slow (due to complicated formula(s)), this method would normally be quicker than filtering since the data to be deleted can be grouped together in one area before deletion.

 

[NdNoviceHlp]

"codes relying on a row-by-row deletion (such as that by NdNoviceHlp can be a priori presumed to be slow in large datasets"... I don't know what your data is but I would be interested in a time trial because it seems to me that if you turn off the screen updating and autocalculation there's nothing else to do but move all those rows up one. I have enjoyed learnin abit more so far in this thread. Dave
ps. not sure what priori means? ...first?

 

[Legacy 14611]

Hi Dave,

How's Manitoba? I only got as far as Alberta, got stuck in Lethbridge for rather too long ...

I don't know what your data is ...

The data used in this thread are those generated by code in post#32

but I would be interested in a time trial

A time trial using these data (and means of doing this) are given in the code in post#40

it seems to me that if you turn off the screen updating and autocalculation

There is no screen updating or autocalculation turned on or off in the code in post#40

there's nothing else to do but move all those rows up one

None of the codes presented in this thread, other than your own, move the rows up by one. You may like to consider ZVI's assessment of how fast, or otherwise, they are (post#54)

ps. not sure what priori means? ...first?

a priori is a Latin phrase sometimes used because there is really no satisfactory concise English equivalent. You can easily Google it. I used in the sense of forming a judgment based on information one already possesses and while still awaiting additional information, after which stage it becomes a posteriori.

HTH Rugila

 

[Boller]

"codes relying on a row-by-row deletion (such as that by NdNoviceHlp can be a priori presumed to be slow in large datasets"... I don't know what your data is but I would be interested in a time trial because it seems to me that if you turn off the screen updating and autocalculation there's nothing else to do but move all those rows up one. I have enjoyed learnin abit more so far in this thread. Dave
ps. not sure what priori means? ...first?

If you want a simple test on some simple data (different from the data presented by the OP), try comparing the run time of your code on the sample data per posts #27 and #28 with the code in those posts.

Even with only one line of code that deletes a range of rows, if the range consists of a large number of non-contiguous areas it will take longer to run than a range with the same number of rows but with fewer non-contiguous areas (obviously one area would be the quickest).
(I'm beginning to sound like a parrot.)

PS. You can check what a priori means in the link I provided in post #55.