VBA for Excel: Error 424 Object Required

Question

I'm finishing a script that verifies if a cell in Column A of Sheet1 ("INCIDENTS") is duplicated at Column A of Sheet2 ("INCDB") and if the cell is duplicate it deletes the whole row in Sheet1.

The problem is that after the first loop (and deleting the row) it gives me the 424 error and highlights If iSrc.Cells.Value = iDst.Cells.Value Then

Any ideas on the cause? Here's the code:

Sub CTDeleteDuplicatePaste()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim iSrc As Variant
Dim iDst As Variant
Dim rng As Range

Set ws1 = Sheets("INCIDENTS")
Set ws2 = Sheets("INCDB")

For Each iSrc In ws1.Range("A5:A9999" & LastRow)
    For Each iDst In ws2.Range("A5:A9999")
        If iSrc.Cells.Value = iDst.Cells.Value Then
        If rng Is Nothing Then
            Set rng = iSrc.EntireRow
        Else
            Set rng = Union(rng, iSrc.EntireRow)
        End If
            rng.EntireRow.Delete

        End If

    Next iDst
Next iSrc

End Sub

Try defining your range in reverse, e.g. A9999:A5. I'm curious if that would fix the issue since the behavior of Excel is pull rows up once you delete them. — Marc
– Marc, Commented Oct 26, 2015 at 20:48
I would also replace the if rng ... End If rng.EntireRow.Deletewith just iSrc.EntireRow.Delete because there is no sense in taking the Union of a range that you have already deleted. You then don't need the rng variable either. — trincot
– trincot, Commented Oct 26, 2015 at 20:59
@findwindow removing the .cells didn't change anything. It was able to delete one line and then it stopped. The debugger marked the same line. — Ugo Portela Pereira
– Ugo Portela Pereira, Commented Oct 26, 2015 at 21:01

Community · Accepted Answer · 2017-05-23 10:27:09Z

1

I'd do it without objects iSrc and iDst. And from reverse order - this code worked for me:

Sub CTDeleteDuplicatePaste()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Long
Dim j As Long

Set ws1 = Sheets("INCIDENTS")
Set ws2 = Sheets("INCDB")

For i = 9 To 5 Step -1        'change 9 to 9999 for your real data
    For j = 9 To 5 Step -1    'change 9 to 9999 for your real data
        If Len(ws1.Cells(i, 1).Value) > 0 Then
            If ws1.Cells(i, 1).Value = ws2.Cells(j, 1).Value Then
                ws1.Cells(i, 1).EntireRow.Delete
                GoTo nextIteration
            End If
        End If
    Next
nextIteration:
Next

End Sub

Regarding the performance issue of .EntireRow.Delete, this is the additional reading:
Tests on processing 1 million rows
Solution employing Sorting

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Oct 26, 2015 at 20:59

ZygD

24.8k41 gold badges106 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Ugo Portela Pereira Over a year ago

I'm going home and I will try that when I'm there. Do I have to Dim i and j as Variables? I'm trying to understand the properties concept in VBScript and programming in general.

ZygD Over a year ago

Yes, you need to dim them - As Long. Also, I edited your main question - you don't use VBScript here. It's something similar, but it's called VBA.

Ugo Portela Pereira Over a year ago

I think something went wrong. The code has been running for quite a while (10 minutes!!) and it only says that it's still running. I've pasted the code between set ws2 and End Sub. And before that I set i and j As Long. I had to shut Excel from Task Manager.

Ugo Portela Pereira Over a year ago

it is something I had from another script. I ran this with i = 999 and j = 999 and it worked way better. One thing I found weird was that it deleted all the lines from 999 to 5 that didn't meet the value. Is that because "" = ""? If so, how could we add an exception?

ZygD Over a year ago

Exactly. I have updated the code with one additional If to check if a cell in ws1 is not empty. Also, if using LastRow you can just replace 9999 with LastRow and that should be fine.

|

Collectives™ on Stack Overflow

VBA for Excel: Error 424 Object Required

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related