Excel VBA Object Required Error

SiddharthK91

New Member
Joined
Nov 1, 2018
Messages
7
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!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:
Code:
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
Do you always want it to go down to the last populated cells in column M in each sheet?
If so, try this:
Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
Wow, that "str = " on the delete line was a typo on my part.
Change this line:
Code:
[COLOR=#333333]str = Sheets("Sheet1").Cells(r, "M").Range("B:N").Delete(xlShiftUp)[/COLOR]
to this:
Code:
[COLOR=#333333]Sheets("Sheet1").Range(Cells(r, "B"), Cells(r,"N")).Delete(xlShiftUp)[/COLOR]
 
Upvote 0
Hi Joe4, shouldn't that be...

Code:
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

Code:
With Sheets("Sheet1")
   .Range(.Cells(r, "B"), .Cells(r,"N")).Delete(xlShiftUp)
End With
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,123
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top