Delete ROW based on another sheet list value

MMarius

Board Regular
Joined
Sep 15, 2015
Messages
60
Hello,

I have in sheet1 over 1000 rows, in sheet2 i have the rows that needs to be deleted.

E.G.:

Sheet1:
A1: 50
A2: 500
A3: 250
A4: 300
A5: 540
A6: 532
A7: 483
A8: 349
A9: 212
A10: 132


Sheet2 (file with list that needs to be removed):

250
300
540
212



So, I want a macro that searches for value in "Sheet2" and if finds it in "Sheet1", to delete the entire row.

Based on that, for my example it will remain afterwards:

A1: 50
A2: 500
A3: 532
A4: 483
A5: 349
A6: 132

Thank you :)
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
How about
Code:
Sub MMarius()
   Dim Ary As Variant
   
   With Sheets("sheet2")
      Ary = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2)
   End With
   With Sheets("sheet1")
      .Range("A1").AutoFilter 1, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
How about
Code:
Sub MMarius()
   Dim Ary As Variant
   
   With Sheets("sheet2")
      Ary = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2)
   End With
   With Sheets("sheet1")
      .Range("A1").AutoFilter 1, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

Hello, Fluff.

It works like intended for letters, for numbers it doesn't do anything.
If i tried putting letters, it worked fine and removed like intended. I also formated cells to numbers and still same (it was general).

Thank you
 
Upvote 0
Ok, how about
Code:
Sub MMarius()
   Dim Ary As Variant
   Dim i As Long
   
   With Sheets("sheet2")
      Ary = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   For i = 1 To UBound(Ary)
      Ary(i) = CStr(Ary(i))
   Next i
   With Sheets("sheet1")
      .Range("A1").AutoFilter 1, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
Ok, how about
Code:
Sub MMarius()
   Dim Ary As Variant
   Dim i As Long
   
   With Sheets("sheet2")
      Ary = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   For i = 1 To UBound(Ary)
      Ary(i) = CStr(Ary(i))
   Next i
   With Sheets("sheet1")
      .Range("A1").AutoFilter 1, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

Many thanks, works as intended :) Can be closed.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You're welcome & thanks for the feedback

Hello Fluff,

When I put many data [i have like 4000-6000 rows] (even duplicates), It throws

"Run-time error '1004':
application-defined or object-defined error"

The code that I`m using is

Code:
Sub MMarius()
   Dim Ary As Variant
   Dim i As Long
   
   With Sheets("sheet2")
      Ary = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   For i = 1 To UBound(Ary)
      Ary(i) = CStr(Ary(i))
   Next i
   With Sheets("sheet1")
      .Range("A1").AutoFilter 1, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

In sheet2 i have the values that I wish to delete from sheet1.

Thank you
 
Last edited:
Upvote 0
Which line gives the error?
 
Upvote 0
I put my names in "sheet2" (4000-5000 names), some are duplicates. The VBA code i put in sheet2. I only use the A column

If i leave 100-200 names, it works, the code is running.

It's maybe because in Sheet1, the A column has over 40.000 entries? With small entries in sheet2, works, if i put a lot more data, it doesn't.

It may have to do that some are with different characters? (*;'.[]./;][}{...etc)


Thank you
 
Upvote 0
When you get the error which line is highlighted when you click debug?
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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