Delete Rows with value not found on Sheet2

[morkar]

Hello,

I have an excel workbook with a sheet "ExportedData" in column D, there is a value, if that value does not exist in another sheet, Sheet2, in my workbook I would like to delete the row. Sheet2 has numeric values in column A, A1-A85. This range can increase over time. The ExportedData sheet has over 200K rows, in columns A-L. If the value in Column D does not exist in Sheet2, column A, I would like the row to be deleted.

Thanks!
K

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub morkar()
   Dim Cl As Range
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
   Dim Dic As Object
  
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet2")
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets("ExportedData")
      Ary = .Range("D1", .Range("D" & Rows.Count).End(xlUp)).Value2
   End With
   ReDim Nary(1 To UBound(Ary))
   For r = 1 To UBound(Ary)
      If Not Dic.Exists(Ary(r, 1)) Then
         nr = nr + 1
         Nary(nr) = Ary(r, 1)
      End If
   Next r
   With Sheets("ExportedData")
      .Range("A1:L1").AutoFilter 4, Nary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[morkar]

Hi & welcome to MrExcel.
How about

Sub morkar()
   Dim Cl As Range
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
   Dim Dic As Object
 
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet2")
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets("ExportedData")
      Ary = .Range("D1", .Range("D" & Rows.Count).End(xlUp)).Value2
   End With
   ReDim Nary(1 To UBound(Ary))
   For r = 1 To UBound(Ary)
      If Not Dic.Exists(Ary(r, 1)) Then
         nr = nr + 1
         Nary(nr) = Ary(r, 1)
      End If
   Next r
   With Sheets("ExportedData")
      .Range("A1:L1").AutoFilter 4, Nary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

HI, I added the new module and ran the macro but it is not deleting the data. I double checked the sheet names, etc. Any thoughts?

 

[Fluff]

If you put a breakpoint on this line

      .AutoFilter.Range.Offset(1).EntireRow.Delete

(to do that put the cursor on that line & press F9) Run the code & when it stops have a look at the data sheet. Is there anything visible other than row 1?

 

[morkar]

N

If you put a breakpoint on this line

      .AutoFilter.Range.Offset(1).EntireRow.Delete

(to do that put the cursor on that line & press F9) Run the code & when it stops have a look at the data sheet. Is there anything visible other than row 1?

No, only row 1 is showing when I do this.

 

[Fluff]

Oops, missed the fact that you are dealing with numbers, change the line in red as shown

   For r = 1 To UBound(Ary)
      If Not Dic.Exists(Ary(r, 1)) Then
         nr = nr + 1
         Nary(nr) = CStr(Ary(r, 1))
      End If
   Next r

 

[morkar]

That seemed to work, but then it mailed to delete rows after the 4th value in Sheet2. Any thoughts?

 

[Fluff]

Sorry, but not sure what you're saying.

 

[morkar]

Sorry, but not sure what you're saying.

Sorry, the macro worked and deleted up to the 4th value on the Sheet2, after that it did not delete the rows. For example, if the values in column D on the sheet titled "ExportedData" are 1-130 and the values in Column A on Sheet2 are 1,2,3,5,9,12,15,etc. The macro deleted rows with a value of 4,6,7,8, but then did not delete rows that included values of 10,11,13,14. Hope that example helps.

 

[morkar]

Ne ver mind - it worked! Thanks so much!