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

 

[si3po]

Are you saying that if B5 & L5 combined are not found on the import sheet delete that row?

If you mean that if B5:L5 on 'Master Sheet' do not match any row in B:L of 'Import Sheet', then yes it should be deleted from 'Master Sheet'.

The opposite (B5:L5 only appear on 'Import Sheet') is possible as the 'Import Sheet' will contain new orders that won't yet be on the 'Master Sheet'.

 

[Fluff]

B5 & L5 is different from B5:L5.
Can you please state in simple terms the EXACT criteria for deleting a row.

 

[si3po]

B5 & L5 is different from B5:L5.
Can you please state in simple terms the EXACT criteria for deleting a row.

sorry, i thought i had...

So, if cells in col.B thru col.L of Row 5 (i.e. B5:L5) on 'Master Sheet' do not match any row of columns B thru Lof 'Import Sheet' (i.e. in the range B10:L5000) then the row on 'Master Sheet' can be deleted.

 

[Fluff]

Ok, what version of Xl are you using?

 

[si3po]

Ok, what version of Xl are you using?

we have Xl 2013 and 2016 in the office.

 

[Fluff]

In that case try

Sub si3po()
    Dim MasterSht As Worksheet, ImportSht As Worksheet
    Dim x As Long
    Dim ImpAry As Variant
    
    Application.ScreenUpdating = False
    Set MasterSht = ThisWorkbook.Sheets("pcode")
    Set ImportSht = ThisWorkbook.Sheets("watched")
    
    ImpAry = ImportSht.Range("[COLOR=#ff0000]A1[/COLOR]").CurrentRegion.Value2
    With CreateObject("Scripting.dictionary")
        For x = 2 To UBound(ImpAry)
            .Item(Join(Application.Index(ImpAry, x, Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)), "|")) = Empty
        Next x
            For x = MasterSht.Cells(Rows.Count, "G").End(xlUp).Row To 9 Step -1
                If Not .exists(Join(Application.Index(MasterSht.Range("B" & x).Resize(, 11).Value, 1, 0), "|")) Then
                    MasterSht.Rows(x).Delete                                                                                                                 ' delete row if no match
                End If
            Next x
    End With
Application.ScreenUpdating = True
    
End Sub

Change range in red to match the first cell in the import sheet

 

[si3po]

Thanks so much.

i'd started looking at using a helper cell on each row (on both sheets) that contained the contents of B:L concatenated to a single string, thn using a =IF(Match(....) formula and picking out the non-matches with VBA and deleting those rows.

Looking at your VBA, am i right in assuming that it works on a similar vein, but instead takes the value of cells B:L in the row on the 'Import Sheet' and puts them to a temporary dictionary where they can be compared to the rows on 'Master Sheet', with any row on the 'Master Sheet' that doesnt match being deleted.

 

[Fluff]

That's right
I was going to suggest the helper cell route if you had the textjoin function, but as you don't decided it was easier to do it in code.