How can I compare 2 columns on seperate sheets to delete records not found?

[mecerrato]

I have 2 sheets, I want to search the loan number in column A of my "Main" sheet and compare it to Column C if my "data" worksheet. If it is not found I want a msgbox to ask if I should delete the row from the "Main" sheet. I can so the delete and the msgbox but I really don't know how to write the search code. I tried a few things from a google search but can't get it done.

 

[Peter_SSs]

It looks like your two tables might be formal Excel tables (ListObjects). If that is so, see if you can adapt this.
I have assumed the table on 'Main' is named "tblMain" and the table on 'Data' is named "tblData" (& that it is ListObject(1) on that sheet) so adjust the code to match that (& check the header values on the relevant columns)

Dim cell As Range, rngData As Range, LoanFound As Range

With Sheets("Data")
  .ListObjects(1).AutoFilter.ShowAllData
  Set rngData = .Range("tblData[Loan '#]")
End With
For Each cell In Range("tblMain[Loan '#]")
  Set LoanFound = rngData.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
  If LoanFound Is Nothing Then
    'Put your code here for MsgBox/Delete etc
   
  End If
Next cell

 

[mecerrato]

It looks like your two tables might be formal Excel tables (ListObjects). If that is so, see if you can adapt this.
I have assumed the table on 'Main' is named "tblMain" and the table on 'Data' is named "tblData" (& that it is ListObject(1) on that sheet) so adjust the code to match that (& check the header values on the relevant columns)

Dim cell As Range, rngData As Range, LoanFound As Range

With Sheets("Data")
  .ListObjects(1).AutoFilter.ShowAllData
  Set rngData = .Range("tblData[Loan '#]")
End With
For Each cell In Range("tblMain[Loan '#]")
  Set LoanFound = rngData.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
  If LoanFound Is Nothing Then
    'Put your code here for MsgBox/Delete etc

  End If
Next cell

Thank you for the reply. This code gives me a subscript out of range error, the debugger stops at this line:
.ListObjects(1).AutoFilter.ShowAllData
I am not using official tables, I am simplys using filters

 

[mecerrato]

I tried something like this but the debugger stopped at Set rngData = .Range("C11:C" & .Range("C" & Rows.Count).End(xlUp).Row).Value2 with an object not found error.

Sub Delete_Closed_Loans()
ActiveSheet.Unprotect Password:="Mortgage1"
Dim cell As Range, rngData As Range, rngMain As Range, LoanFound As Range

With Sheets("Data")
'  .ListObjects(1).AutoFilter.ShowAllData
  Set rngData = .Range("C11:C" & .Range("C" & Rows.Count).End(xlUp).Row).Value2
End With
With Sheets("Main")
   Set rngMain = .Range("A8:A" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With

For Each cell In rngMain
  Set LoanFound = rngData.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
  If LoanFound Is Nothing Then
    'Put your code here for MsgBox/Delete etc
    MsgBox "Hello " & LoanFound
  
  End If
Next cell
End Sub

 

[Peter_SSs]

I tried something like this but the debugger stopped at Set rngData = ...

Yes, that line is incorrect because you are trying to set a range equal to a value. Similar for the other range.
Also, for the Find method to work, the value being searched for must be visible so we do need to make sure that the 'Data' sheet is not filtered, so I have put an equivalent line back in the code.
Your MsgBox would also produce an error for the Loan numbers not found.

Try these changes

Sub Delete_Closed_Loans_v2()
  ActiveSheet.Unprotect Password:="Mortgage1"
  Dim cell As Range, rngData As Range, rngMain As Range, LoanFound As Range
  
  With Sheets("Data")
    If .FilterMode Then .ShowAllData
    Set rngData = .Range("C11:C" & .Range("C" & Rows.Count).End(xlUp).Row).Value2
    Set rngData = .Range("C11:C" & .Range("C" & Rows.Count).End(xlUp).Row)
  End With
  With Sheets("Main")
     Set rngMain = .Range("A8:A" & .Range("A" & Rows.Count).End(xlUp).Row).Value
     Set rngMain = .Range("A8:A" & .Range("A" & Rows.Count).End(xlUp).Row)
  End With
  
  For Each cell In rngMain
    Set LoanFound = rngData.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If LoanFound Is Nothing Then
      'Put your code here for MsgBox/Delete etc
      MsgBox "Hello " & LoanFound
      MsgBox cell.Value & " loan not found"
    End If
  Next cell
End Sub

 

[mecerrato]

Yes, that line is incorrect because you are trying to set a range equal to a value. Similar for the other range.
Also, for the Find method to work, the value being searched for must be visible so we do need to make sure that the 'Data' sheet is not filtered, so I have put an equivalent line back in the code.
Your MsgBox would also produce an error for the Loan numbers not found.

Try these changes

Sub Delete_Closed_Loans_v2()
  ActiveSheet.Unprotect Password:="Mortgage1"
  Dim cell As Range, rngData As Range, rngMain As Range, LoanFound As Range
 
  With Sheets("Data")
    If .FilterMode Then .ShowAllData
    Set rngData = .Range("C11:C" & .Range("C" & Rows.Count).End(xlUp).Row).Value2
    Set rngData = .Range("C11:C" & .Range("C" & Rows.Count).End(xlUp).Row)
  End With
  With Sheets("Main")
     Set rngMain = .Range("A8:A" & .Range("A" & Rows.Count).End(xlUp).Row).Value
     Set rngMain = .Range("A8:A" & .Range("A" & Rows.Count).End(xlUp).Row)
  End With
 
  For Each cell In rngMain
    Set LoanFound = rngData.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If LoanFound Is Nothing Then
      'Put your code here for MsgBox/Delete etc
      MsgBox "Hello " & LoanFound
      MsgBox cell.Value & " loan not found"
    End If
  Next cell
End Sub

Thanks, this worked great, one last thing, I am trying to identify the row so I can delete row if duplicate. I have modified the code below but need to know how to identify the row. The part of the code below where it says Selection.EntireRow.Delete should have the row number rather than the word selection but I don't see how to identify the row with the duplicate.

Sub Delete_Closed_Loans()
ActiveSheet.Unprotect Password:="Mortgage1"
Dim cell As Range, rngData As Range, rngMain As Range, LoanFound As Range
Dim MSG1 As String

With Sheets("Data")
  Set rngData = .Range("C11:C" & .Range("C" & Rows.Count).End(xlUp).Row)
End With
With Sheets("Main")
   Set rngMain = .Range("A8:A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With

For Each cell In rngMain
  Set LoanFound = rngData.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
  If LoanFound Is Nothing Then
    MSG1 = MsgBox(cell.Value & " " & cell.Offset(0, 1).Value & " loan not found, delete?", vbYesNoCancel)
    If MSG1 = vbYes Then
        Selection.EntireRow.Delete
    ElseIf MSG1 = vbNo Then
    Else
    Exit Sub
    End If
  End If
Next cell
End Sub

 

[Peter_SSs]

1. If you are going to be deleting rows on your way through then you need to work from the bottom up or you will likely miss even checking some rows.

For example, if you are checking cells A8:A10 and when checking A8 you decide to delete that row, A9 moves up to A8 but your have already checked A8 so now we check A9 (which was A10 but has moved up). Therefore we have missed checking what was originall in A9.

2. I not that you appear to be disregarding my advice about making sure the data is not filtered. Assuming you want to check all values in column A (from row 8) of Main against all values in column C (from row11) of Data then both sheets should be confirmed to be unfiltered before running this code otherwise values will at some stage be missed!

3. To delete the row relating to the MsgBox use

cell.EntireRow.Delete