Delete ROW based on another sheet list value

[MMarius]

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

 

[Fluff]

How about

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

 

[MMarius]

How about

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

 

[Fluff]

Ok, how about

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

 

[MMarius]

Ok, how about

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.

 

[Fluff]

You're welcome & thanks for the feedback

 

[MMarius]

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

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

 

[Fluff]

Which line gives the error?

 

[MMarius]

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

 

[Fluff]

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