VBA Help - "Delete Method of Range class failed"

si3po

Board Regular
Joined
Jan 7, 2019
Messages
98
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi folks, i'm getting a Run-Time Error '1004' "Delete method of Range class failed" for the following code.

Code:
Sub Satisfied()

Dim MasterSht As Worksheet
Dim ImportSht As Worksheet
Dim LastRow As Long
Dim x As Long

Application.ScreenUpdating = False
Set MasterSht = ThisWorkbook.Sheets("Master Sheet")
Set ImportSht = ThisWorkbook.Sheets("Import Sheet")

With MasterSht
    LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row

    For x = LastRow To 9 Step -1                                                                             ' always loop backwards when deleting rows
        If IsError(Application.Match(.Range("G" & x).Value, ImportSht.Range("G:G"), 0)) And _
           IsError(Application.Match(.Range("L" & x).Value, ImportSht.Range("L:L"), 0)) Then        'check if there is no match
    .Rows(x).Delete                                                                                                                 ' delete row if no match
        End If
    Next x
End With

Application.ScreenUpdating = True
    
End Sub

The debug shows the line
Code:
.Rows(x).Delete
to be the failure point.

What should happen is each row on 'Master Sheet' is checked for a corresponding row on 'Import' Sheet', if no matching row is found on the 'Import Sheet' then the row on 'Master Sheet' is deleted.

Currenty, the code bugs out when the first non-matching row is found.

***n.b. it is possible for there to different numbers of rows between the two worksheets.***
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Is the master sheet protected?
Also do you have any merged cells on that sheet?
 
Upvote 0
In that case you will need to unprotect the Master sheet.
 
Upvote 0
In that case you will need to unprotect the Master sheet.

Just added that into the code. For some reason however, it doesn't seem to be removing any rows from the 'Master Sheet', even extra rows that i have added to test it is working
 
Upvote 0
In that case step through the code using F8, does it ever get to the line that deletes the row?
 
Upvote 0
In that case step through the code using F8, does it ever get to the line that deletes the row?

stepping through the code, no it doesn't get to the
Code:
.rows(x).delete
line at all; even with a 'test' line placed at the bottom of the 'Master Sheet' data.
 
Upvote 0
In that case for every row either the value in G or the value in L (or both) is found in the import sheet.
 
Upvote 0
In that case for every row either the value in G or the value in L (or both) is found in the import sheet.

That shouldn't be the case however, as the 'test' line i entered into the 'Master Sheet' contains only the value "remove me" in all cells on that row, and it is not entered at all in the 'Import Sheet'.

Apologies for my confusion, i don't proclaim at all to be proficent with VBA by any stretch, but with this code i can't see where it's going wrong at all.
 
Last edited:
Upvote 0
Try running this using F8
Code:
    For x = LastRow To 9 Step -1                                                                             ' always loop backwards when deleting rows
        [COLOR=#ff0000]Debug.Print x, Application.Match(.Range("G" & x).Value, ImportSht.Range("G:G"), 0), .Range("G" & x), _
            Application.Match(.Range("L" & x).Value, ImportSht.Range("L:L"), 0), Range("L" & x)[/COLOR]
        If IsError(Application.Match(.Range("G" & x).Value, ImportSht.Range("G:G"), 0)) And _
           IsError(Application.Match(.Range("L" & x).Value, ImportSht.Range("L:L"), 0)) Then        'check if there is no match
        .Rows(x).Delete                                                                                                                 ' delete row if no match
        End If
    Next x
The part in red will print values to the immediate screen (usually found below the code window) Ctrl G will bring it up if not visible.

Do you see something like
Code:
 26        Error 2042      Remove         Error 2042      Remove
for the test line?
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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