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]

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?

Thanks Fluff, i get the following:

 2879         Error 2042     7670          20           NULL
 2878         Error 2042     7663          20           NULL
 2877          3018          7776          20           NULL
 2876          3020          7767          20           NULL
 2875          3023          7752          20           NULL
 2874         Error 2042     7743          20           NULL
 2873          3026          7736          20           NULL
 2872         Error 2042     7734          20           NULL

 

[si3po]

***solved*** Re: VBA Help - "Delete Method of Range class failed"

Hi all,

I think i've been digging at this too hard... i tried using a 'helper col' at the end of my sheet data with the following formula:

=IF(ISERROR(MATCH(G9,'DEMPROG IMPORT'!G:G,0)),ISERROR(MATCH(L9,'DEMPROG IMPORT'!L:L,0)),"demand extant")

and have amended my VBA to te following:

Sub Satisfied_Dmds()

Dim MasterSht As Worksheet
Dim LastRow As Long
Dim x As Long
'Application.ScreenUpdating = False
Set MasterSht = ThisWorkbook.Sheets("Master Sheet")
With MasterSht
    .Unprotect Password:="5Cm"
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For x = LastRow To 9 Step -1                             ' always loop backwards when deleting rows
        If MasterSht.Range("V" & x).Value = "False" Then     'check if there is no match
        .Rows(x).Delete                                      ' delete row if no match
        End If
    Next x
End With

It works perfectly - all lines that dont match, report 'False' in Col.V the code above then searches for all rows reporting a 'False' in Col.V and deletes tht row.

 

[Fluff]

Ok, that means that the 2nd part of your If statement, looking at column L, isn't giving an error. Hence no rows are being deleted.
Whilst I'm not sure what you have in col L for all those rows, it is being found on row 20 of the import sheet.

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[si3po]

Glad you sorted it & thanks for the feedback

i may have been premature in my rejoicing, it seems my 'solution' doesn't fully work as intended.

Whilst it does delete rows where a match in either column is not made, if the value in Col.G or Col.L on the 'Master Sheet' are found anywhere in the 'Import Sheet' then the line shows as a match. This is problem as it is possible to have the value in Col.G multiple times on each workheet with different associated values in Col.L of the respective sheet.

 

[Fluff]

What are you trying to do?

 

[si3po]

What are you trying to do?

right, i'll try and pare it down to bare necessities....

i hve two sheets, one (import sheet) is updated daily with details of outstanding or unfulfilled demand/orders. The 'Master Sheet' contains progression data for each demand/order. There will always be additional rows on the 'import sheet' where new orders exist, and there will very often be rows on the 'Master Sheet' that no longer exist on the 'Import Sheet' (for example when the order has been satisfied).

What i need to happen with this section of code is remove/delete the rows on the 'Master Sheet' that don't exist on the 'Import Sheet'.

Is that easy enough to understand and look at a soloution for?

 

[Fluff]

I fully understand that, but how do you determine if a row should be deleted? What are the criteria?

 

[si3po]

I fully understand that, but how do you determine if a row should be deleted? What are the criteria?

OK, my apologies.

Let me set the scene so you have a better idea of how I’mtrying to work:

The source for the ‘Import’ data is compiled on a sheet called ‘Export Data’ in another workbook (let’s call it ‘ORDERS’) that contains all orders placed – that workbook works great.

When the order is satisfied, we mark it as such on the ‘Export Data’ sheet and the row is moved (not copied) to another sheet called ‘Completed Orders’, in doing so it does not show when running the code to export data for the ‘IMPORT Sheet’ to be placed into the ‘PROGRESSION’ wb.

We then take the ‘Import Sheet’ from the ‘ORDERS’ WB and place the data within it to my ‘PROGRESSION’ workbook ‘Import Sheet’ overwriting all the existing rows (this works well with existing VBA code).

What I now need to happen (and what I’m struggling with) is this:

Taking the ‘Master Sheet’ and ‘Import Sheet’ on my PROGRESSION wb, using VBA (or formulae), check each row of the ‘Master Sheet’ and compare it with each row of the ‘Import Sheet’. If the row only exists on the 'Master Sheet', then this means that the order has been fulfilled and the row on ‘Master Sheet’ can be deleted in full.

All rows contain an Order Number (Col.G on each worksheet)and a Project ID (Col.L on each sheet), which is why I used them in my previouscode as they were present whatever the row contained. However, I’ve since foundthat Order numbers can be duplicated for orders where multiple components arerequired, where as Project ID is unique to each row (unless where not yet assigned,in which case it is ‘NULL’).

My formula and any previous VBA attempt on my part has only partially succeeded in a solution as I posted previously.

 

[Fluff]

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