Excel VBA Object Required Error

[SiddharthK91]

Hello All,

I am very new to VBA and just beginning to learn. I have a code here to match all the cells of the "M" column in Sheet1 and Sheet3, and delete all the rows from Sheet1 that contain any value from Sheet3's "M" column.
If I go through it using F8, I do not get any error but when I assign it to a button, it fails at run-time with 'Object Required' error. I tried a few things that I found online, but none of them seem to work.
Below is my code. Any help would be much appreciated.

Sub DeleteRows()


Dim rng As Range
Dim rng2 As Range
Dim cell As Object
Dim cell2 As Object

Set rng = Sheets("Sheet1").Range("M2:M1541")
Set rng2 = Sheets("Sheet3").Range("M2:M30")


For Each cell In rng
For Each cell2 In rng2
If cell.Value = cell2.Value Then
cell.EntireRow.Delete
End If
Next
Next


Application.ScreenUpdating = False
End Sub

Thanks in advance!

 

[NdNoviceHlp]

Cell and cell2 are not objects. Trial dimming them as ranges. HTH. Dave
ps. Application.ScreenUpdating needs to be set to True at before ending the sub (false at the start of code)
pps. Welcome to the Board! Also, please use code tags

 

[SiddharthK91]

Thank you for your quick response! I tried dimming them as Ranges, but still getting the same error.
Also, I have updated the Application.ScreenUpdating part.

 

[Joe4]

I think you may be causing issues by deleting the rows in your range at the same time that you are trying to loop through it (and you keep trying to check the row, even if you have deleted it).

Whenever inserting or deleting rows, it is typically better to start from the bottom and work up, so you do not change the size of your unchecked range.
Also, we can avoid the second loop altogether simply by counting how many times it appears in the other list, and delete if that count is greater than 0 (loops are notoriously slow, so it is always good to reduce of eliminate some whenever possible).

Try this:

Sub DeleteRows()

    Dim rng As Range
    Dim r As Long
    Dim str As Variant
    
    Set rng = Sheets("Sheet3").Range("M2:M30")
        
    Application.ScreenUpdating = False
        
'   Loop through all rows on Sheet1 from bottom to top
    For r = 1541 To 2 Step -1
'       Get value from column M in that row
        str = Sheets("Sheet1").Cells(r, "M")
'       See if value is found on sheet 3
        If Application.WorksheetFunction.CountIf(rng, str) > 0 Then
'           Delete row
             Sheets("Sheet1").Cells(r, "M").EntireRow.Delete
        End If
    Next r
    
    Application.ScreenUpdating = True

End Sub

 

[SiddharthK91]

Thank you so much! That worked perfectly and much faster! (I'd used double looping before)
Also, the range I mentioned for both the sheets, (M2:M1541 and M2:M30) wouldn't remain the same. How should I go about doing it dynamically?
For example, in For r = 1541 To 2 Step -1

Thanks for all your help!

 

[Joe4]

Do you always want it to go down to the last populated cells in column M in each sheet?
If so, try this:

Sub DeleteRows()

    Dim rng As Range
    Dim r As Long
    Dim lr1 As Long
    Dim lr3 As Long
    Dim str As Variant
    
    Application.ScreenUpdating = False
    
'   Find last populated row in column M on sheet 3
    lr3 = Sheets("Sheet3").Cells(Rows.Count, "M").End(xlUp).Row
'   Set range
    Set rng = Sheets("Sheet3").Range("M2:M" & lr3)
       
'   Find last populated row in column M on sheet 1
    lr1 = Sheets("Sheet1").Cells(Rows.Count, "M").End(xlUp).Row
'   Loop through all rows on Sheet1 from bottom to top
    For r = lr1 To 2 Step -1
'       Get value from column M in that row
        str = Sheets("Sheet1").Cells(r, "M")
'       See if value is found on sheet 3
        If Application.WorksheetFunction.CountIf(rng, str) > 0 Then
'           Delete row
             Sheets("Sheet1").Cells(r, "M").EntireRow.Delete
        End If
    Next r
    
    Application.ScreenUpdating = True

End Sub

 

[SiddharthK91]

Yes, I wanted it to always go down to the last cell in "M" column. That code works great!
However, I don't want to delete the entire row, and just the range B:N.
I tried this - str = Sheets("Sheet1").Cells(r, "M").Range("B:N").Delete(xlShiftUp)
But it is giving me a '400' error. Any suggestions?

 

[Joe4]

Wow, that "str = " on the delete line was a typo on my part.
Change this line:

[COLOR=#333333]str = Sheets("Sheet1").Cells(r, "M").Range("B:N").Delete(xlShiftUp)[/COLOR]

to this:

[COLOR=#333333]Sheets("Sheet1").Range(Cells(r, "B"), Cells(r,"N")).Delete(xlShiftUp)[/COLOR]

 

[MARK858]

Hi Joe4, shouldn't that be...

Sheets("Sheet1").Range([COLOR="#FF0000"]Sheets("Sheet1").[/COLOR]Cells(r, "B"), [COLOR="#FF0000"]Sheets("Sheet1").[/COLOR]Cells(r,"N")).Delete(xlShiftUp)

or using a With Statement to make it tidier

With Sheets("Sheet1")
   .Range(.Cells(r, "B"), .Cells(r,"N")).Delete(xlShiftUp)
End With

 

[SiddharthK91]

Thank you Joe4! That worked. Really appreciate your help!