Delete/Add Row based upon Lookup Value From other sheet

Question

I have two sheets in the same workbook. Sheet 2 is "DCT Accounts". Sheet 1 is "DCT".

If Column J in "DCT Accounts" says "Add Account", I want to append columns A, B, and C from "DCT Accounts" to the bottom of "DCT".

If Column K in "DCT Accounts" says "Close Account", I want to delete the entire row from "DCT".

I created the following lookup from "DCT Accounts" to "DCT". How do I append or delete rows based upon columns J & K?

=IFERROR(VLOOKUP('DCT Accounts'!A4,DCT!B:B,1,0),VLOOKUP('DCT Accounts'!B4,DCT!B:B,1,0))

Data in both sheets starts with row 4.

DCT

DCT Accounts

"DCT Accounts" is the same story, 700+ rows, but we're using column D as the lookup (which is a formula), but gives us the lookup we want.

Answer 1

You did not answer my clarification question...

So, please test the next code, designed exactly how you suggested. It should be very fast, using arrays and due to that, working in memory and deleting the rows at once:

Sub DeleteAddRow()
 Dim wsDA As Worksheet, wsD As Worksheet, lastRDA As Long, lastRD As Long
 Dim arrDA As Variant, rngDel As Range, arrD As Variant, arrCopy As Variant
 Dim i As Long, j As Long, k As Long
 
 Set wsDA = Worksheets("DCT Accounts")
 Set wsD = Worksheets("DCT")
 lastRDA = wsDA.Range("A" & rows.count).End(xlUp).row
 lastRD = wsD.Range("A" & rows.count).End(xlUp).row
 
 arrDA = wsDA.Range("A4:K" & lastRDA).Value 'place the range in an array
 arrD = wsD.Range("A4:K" & lastRDA).Value   'place the range in an array
 
 ReDim arrCopy(1 To 3, 1 To UBound(arrDA))  'initially redim the array but transposed
                                            'transposed to allow redim preserve to
                                            'the last dimension
 For i = 1 To UBound(arrDA)
    If arrDA(i, 10) = "Add Account" Then
        k = k + 1: arrCopy(1, k) = arrDA(i, 1): arrCopy(2, k) = arrDA(i, 2)
        arrCopy(3, k) = arrDA(i, 3)        'fill the array with the data to be copied
    End If
    If arrDA(i, 11) = "Close Account" Then
        For j = 1 To lastRD
            If arrDA(i, 4) = arrD(j, 2) Then 'create the range to be deleted at once
                If rngDel Is Nothing Then
                    Set rngDel = wsD.Range("A" & j + 3)
                Else
                    Set rngDel = Union(rngDel, wsD.Range("A" & j + 3))
                End If
                Exit For
            End If
        Next j
    End If
 Next i
 if k > 0 Then ReDim Preserve arrCopy(1 To 3, 1 To k)   'keep in the array only non empty elements
 If Not rngDel is Nothing Then rngDel.EntireRow.Delete xlUp             'delete the range at once (very fast)
 lastRD = wsD.Range("A" & rows.count).End(xlUp).row 'determine the last row after deletion
 'drop the array values to the last empty row:
 If k > 0 Then
      wsD.Range("A" & lastRD + 1).Resize(k, 3).Value = _
              WorksheetFunction.Transpose(arrCopy)
 End If
End Sub

From procedural point of view, I think it would be good to clear "Add Account" from the sheet, after inputting the range in the array, or replacing it with something like "Account added". To do this, in order to avoid running the code twice for the same data and double the same account in "DCT".

But, firstly it would be good that you check the logic of determining the range to be copied (from D, or from B column...).