VBA Help - "Delete Method of Range class failed"

[si3po]

Hi folks, i'm getting a Run-Time Error '1004' "Delete method of Range class failed" for the following 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

.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.***

 

[Fluff]

Is the master sheet protected?
Also do you have any merged cells on that sheet?

 

[si3po]

Is the master sheet protected?

Yes! bu**er me, totally over-looked that.

Also do you have any merged cells on that sheet?

no merged cells within the searched range, only in the headers in rows 4 thru 8 on each sheet.

 

[Fluff]

In that case you will need to unprotect the Master sheet.

 

[si3po]

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

 

[Fluff]

In that case step through the code using F8, does it ever get to the line that deletes the row?

 

[si3po]

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

.rows(x).delete

line at all; even with a 'test' line placed at the bottom of the 'Master Sheet' data.

 

[Fluff]

In that case for every row either the value in G or the value in L (or both) is found in the import sheet.

 

[si3po]

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.

 

[Fluff]

Try running this using F8

    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

 26        Error 2042      Remove         Error 2042      Remove

for the test line?