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?

 

[Blade Hunter]

Yes in the loop you could build a rather large range and delete all at once.

 

[Blade Hunter]

'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

 

[PoggiPJ]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	part_number	part_description	software_brand	item_points	charge_unit	svp_bl	svp_d	svp_e	svp_f	svp_g	svp_h	svp_i	svp_j	svp_ed	svp_gv	rnwl_part_number	fct_part_flag	fct_acqstn_code	price_start_date	price_end_date	part_eol_date	country	iso_currncy_code	reseller_authorization
2	D039ULL	Rational Appsc	Rational	15343	User	32500	32500	32500	32500	32500	32500	32500	32500	32500	32500	E04AVLL	1	WTFR	2/1/2008	12/31/2009		USA	USD
3	D03A5LL	Datamirror Con	DM	304	Value Unit	131	131	131	131	131	131	131	131	131	131	E04BELL	1	DTMR	1/1/2009	12/31/2009		USA	USD
4	D03A6LL	Datamirror Con	DM	1003	Connection	7480	7480	7480	7480	7480	7480	7480	7480	7480	7480	E04BFLL	1	DTMR	1/1/2009	12/31/2009		USA	USD
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?

DelRows = Right(DelRows, Len(DelRows) - 1)

 

[Blade Hunter]

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

 

[PoggiPJ]

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?

 

[PoggiPJ]

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.

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?

 

[Blade Hunter]

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?

That is spot on .

 

[Blade Hunter]

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.

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?

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

Cheers

Dan

 

[xld]

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

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

But a filter is the fastest way to delete data.