Faster Way to Delete Rows?

PoggiPJ

PoggiPJ

Active Member
Joined
Mar 25, 2008
Messages
330
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.
Code: 
'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.
Code: 
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Yes in the loop you could build a rather large range and delete all at once.
 
Last edited:
Upvote 0
Code: 
'Put this at the top somewhere
Dim DelRows As String
 
    If ThisPartIsNotInTheContract Then
        'Since this part isn't included in the contract, delete it.
        DelRows = DelRows & "," & RefTableRow & ":" & RefTableRow 'Replaces your current delete call
        deletedRecordCount = deletedRecordCount + 1
    End If
 
    'Put this after the loop
    DelRows = Right(DelRows, Len(DelRows) - 1) 'Remove the Comma at the start
    Rows(DelRows).Delete

I have freetyped this so there may be some debugging to do. Make sure you make a BACKUP of your sheet BEFORE running any code.

Cheers

Dan
 
Upvote 0
Hi Dan, Thanks. But I'm not yet exactly clear on what you're proposing.

I do see that we're postponing the actual delete until the end, and doing it all at once. So we are creating a really long string that consists of each record to be deleted? Does it matter that each row in the reference table contains about 25 columns of price data? For example, here are a few rows.
Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
1part_numberpart_descriptionsoftware_branditem_pointscharge_unitsvp_blsvp_dsvp_esvp_fsvp_gsvp_hsvp_isvp_jsvp_edsvp_gvrnwl_part_numberfct_part_flagfct_acqstn_codeprice_start_dateprice_end_datepart_eol_datecountryiso_currncy_codereseller_authorization
2D039ULLRational AppscRational15343User32500325003250032500325003250032500325003250032500E04AVLL1WTFR2/1/200812/31/2009USAUSD
3D03A5LLDatamirror ConDM304Value Unit131131131131131131131131131131E04BELL1DTMR1/1/200912/31/2009USAUSD
4D03A6LLDatamirror ConDM1003Connection7480748074807480748074807480748074807480E04BFLL1DTMR1/1/200912/31/2009USAUSD
DSWPrices



After the loop is finished, we still have that original big reference table, and now this really long string of records.

Can you explain what is happening in this statement?
Code: 
DelRows = Right(DelRows, Len(DelRows) - 1)
 
Upvote 0
OK, Whilst we build DelRows we are adding a new row to it each time the criteria is satified, in order for it to be useful to us the rows have to be comma seperated, this is done like so:

DelRows = DelRows & "," & RefTableRow & ":" & RefTableRow

As you can see the first thing it does is add a comma to the existing delrows then add the new rows.

This is fine but the first time it adds something Delrows is blank so lets say the first row to be deleted is row three.

Delrows would now = ",3:3"

We add row 5 and it would become:

",3:3,5:5"

So the string is correct except the comma at the start so I use the RIGHT command to take all the chars counting from the right of DelRows and I count in the length of ALL chars in DelRows minus 1 (The comma).

",3:3,5:5" would now be "3:3,5:5"

That string is now something we can pass to a rows().delete command and it should kill them all in one go.

The Rows().delete command will remove ALL columns for those rows, the amount of columns with data in is irrelevant.

Cheers

Dan
 
Upvote 0
Ahh that helps tremendously. So Delrows is not the actual content of the rows, it's a long string of only the row numbers. All the while, whenever it is determined that a row is not needed, we capture the row number instead of deleting it on the spot. Once we get to the end of the loop, we delete all the rows in one command. Almost like selecting with the Ctrl key. Did I understand you correctly?
 
Upvote 0
Ok, i've restructured my code. Everything works smoothly until the actual delete statement, where I'm getting a Type Mismatch. I know there is a syntax issue, but I can't figure it out.
Code: 
Private Sub CompressReferenceTable(ByRef RefTableRange As Range)
Dim ThisPartIsNotInTheContract As Boolean
Dim x As Long
Dim record As Long
Dim RefTableRow As Long
Dim DelRows As String
    
    RefTableRow = 1   'to skip the header (first) row
    Application.Calculation = xlCalculationManual
    While RefTableRow < RefTableRange.Rows.Count
        RefTableRow = RefTableRow + 1
        
        'begin with the assumption that this part will NOT be in the contract and we will delete it.
        ThisPartIsNotInTheContract = True
        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, add it to the delete list
            DelRows = DelRows & "," & RefTableRow & ":" & RefTableRow
            'RefTableRange.Cells(RefTableRow, 1).EntireRow.Delete
            deletedRecordCount = deletedRecordCount + 1
        End If
    Wend
    
    DelRows = Right(DelRows, Len(DelRows) - 1) 'Remove the Comma at the start
    [COLOR=Red][B]RefTableRange.Rows(DelRows).Delete[/B][/COLOR]
    
End Sub
Any ideas on how I need to change that statement?
 
Upvote 0
PoggiPJ said:
Ahh that helps tremendously. So Delrows is not the actual content of the rows, it's a long string of only the row numbers. All the while, whenever it is determined that a row is not needed, we capture the row number instead of deleting it on the spot. Once we get to the end of the loop, we delete all the rows in one command. Almost like selecting with the Ctrl key. Did I understand you correctly?
Click to expand...

That is spot on :).
 
Upvote 0
PoggiPJ said:
Ok, i've restructured my code. Everything works smoothly until the actual delete statement, where I'm getting a Type Mismatch. I know there is a syntax issue, but I can't figure it out.
Code: 
Private Sub CompressReferenceTable(ByRef RefTableRange As Range)
Dim ThisPartIsNotInTheContract As Boolean
Dim x As Long
Dim record As Long
Dim RefTableRow As Long
Dim DelRows As String
 
    RefTableRow = 1   'to skip the header (first) row
    Application.Calculation = xlCalculationManual
    While RefTableRow < RefTableRange.Rows.Count
        RefTableRow = RefTableRow + 1
 
        'begin with the assumption that this part will NOT be in the contract and we will delete it.
        ThisPartIsNotInTheContract = True
        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, add it to the delete list
            DelRows = DelRows & "," & RefTableRow & ":" & RefTableRow
            'RefTableRange.Cells(RefTableRow, 1).EntireRow.Delete
            deletedRecordCount = deletedRecordCount + 1
        End If
    Wend
 
    DelRows = Right(DelRows, Len(DelRows) - 1) 'Remove the Comma at the start
    [COLOR=red][B]RefTableRange.Rows(DelRows).Delete[/B][/COLOR]
 
End Sub
Any ideas on how I need to change that statement?
Click to expand...


My bad, change RefTableRange.Rows(DelRows).Delete to RefTableRange.Range(DelRows).Delete

Cheers

Dan
 
Upvote 0
You don't need a lopp to check if that value is in the array, just use

Code: 
            ThisPartIsNotInTheContract = IsError(Application.Match(RefTableRange.Cells(RefTableRow, 1), PAPartArray, 0))

But a filter is the fastest way to delete data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel VBA to delete rows from workbooks from specific data set
Replies
4
Views
2K
Chase_Luke
C
C
  • Question Question
Use Row Data to Create New Sheet from Template and Reference Data
Replies
0
Views
636
ChrisMas92
C
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
539
sspatriots
S
A
  • Solved
2 identical master spreadsheets merging to make 3rd spreadsheet containing both sets of data in simple and safe way!
Replies
2
Views
598
Avril
A
A
  • Question Question
Deleting empty rows fastest way possible
2
Replies
14
Views
917
jolivanes
jolivanes

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top