title: Deleting rows based on multiple criteria across 2 workbooks in VBA
body: <p>I have 2 workbooks. 1 used as the Master and 1 that it ran out of Oracle Cloud on a daily basis with new information relevant to the Master Workbook. I want to compare two values between workbooks and if there is a match delete the corresponding row in the Master spreadsheet. The columns in question are column A in both workbooks which is a unique number identifying the invoice and column AB which either is "PAID" or "UNPAID" but should only be used to determine if the row should be deleted or updated between workbooks.</p>
  
  <p>If the value in column AB is "PAID" and is in the daily workbook(let's call it WB2) and the value in column A of WB2 is in Master then delete the row in Master.</p>
  
  <p>Below is the code that i've currently got. I've went down the route of putting both the ranges in an array and then was hoping to do something like check if the values in the array match (which i've done previously) but I'm not really sure how to then move onto deleting the corresponding row from the correct WB when the conditions have been met.</p>
  
  <pre><code>Public startCell As Range
  
  Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim i As Long
    Dim j As Integer
    j = 0
  
    For i = LBound(arr) To UBound(arr)
      If arr(i, 1) = stringToBeFound Then
          IsInArray = True
          j = 1
      End If
    Next i
    If j = 0 Then IsInArray = False
  End Function
  
  Sub AmendMaster()
  
  Set startCell = ActiveWorkbook.Sheets("Main_Data").Cells(Rows.Count, 
  "A").End(xlUp)
  
  Dim mainRng As Range
  Dim newRng As Range
  
  Dim cell As Range
  
  Dim mainArr As Variant
  
  Dim wb As Workbook
  Dim wbs(1) As Workbook
  
  
  Dim i As Long
  Dim j As Long
  Dim RowMatch As Long
  a = 0
  
  Set mainRng = ActiveWorkbook.Sheets("Main_Data").Range("A2", ActiveWorkbook.Sheets("Main_Data").Cells(Rows.Count, "A").End(xlUp).Offset(0,27)) 'Column A is the Cust Transaction ID on the Main Report
  
  
  mainArr = mainRng.Value
  
  For Each wb In Workbooks
  
  Set wbs(a) = wb
  a = a + 1
  
  Next wb
  
  Set newRng = wbs(1).Sheets("Sheet1").Range("A3", wbs(1).Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(0,28)) 'Column A is the Cust Transaction ID on the Daily Report
  newArr = newRng.Value
  
  For Each cell In mainRng
      cell.Value = cell.Value * 1
  Next cell
  For Each cell in newRng
      cell.Value = cell.Value * 1
  Next cell
  
  newRng.NumberFormat = "0"
  newRng.NumberFormat = "0"
  
  mainRng = newRng.Value
  
  
  For i = 1 To UBound(newArr)
  match = False
  For j = 1 To UBound(mainArr)
      If newArr(i,28) = "PAID" And newArr(i,1) = mainArr(j,1) THEN 
          .Rows(i).Delete
      End If
  Next i
  
  
  End Sub
  </code></pre>
  
  <p>The expected result should be if the Master spreadsheet has 10 rows that are in  WB2 and those 10 rows are in WB2 also have "PAID" in column AB then the VBA should delete the 10 matching rows in Master. If AB is "UNPAID" then although it may match then it just needs to copy and overwrite all the columns from A:V in Master workbook but for the purpose of this post i'm just wanting to focus on deleting rows as I'm pretty sure I could figure out pasting values.</p>
  
  <p>Any help would be great as I've tried to understanding deleting values but not sure how to do it via array or even what's the best way to tackle this problem so i'm completely open to any tips or suggestions so I can improve coding.</p>
  
  <p>If there's anything else that you need from me to better understand the problem or try please don't hesitate to let me know.</p>
  
  <p>Regards,</p>
  
  <p>Matt</p>