title: Duplicate Removal using an array in vba
body: <p>Hi have used some code from an answer to a question <a href="https://stackoverflow.com/questions/13665305/how-do-i-delete-duplicates-between-two-excel-sheets-quickly-vba">'How do I delete duplicates between two excel sheets quickly vba'</a> and tried to alter this code to suite my own VBA script.  the code does delete rows the same amount as to what is in the array but it is just deleting the first 11 rows.  I am fairly new to VBA and not completely understanding why it is doing this.  Below is a copy of the script I am using.</p>
  
  <pre><code>    Dim overLayWB As Workbook       'Overlay_workbook
      Dim formattedWB As Workbook     'Formatted_workbook
      Dim formattedWS As Worksheet    'Current active worksheet (Formatted)
      Dim overLayWS As Worksheet      'Worksheet in OverLay
      Dim lastRowFormatted As Long
      Dim lastRowOverLay As Long
  
      Dim targetArray, searchArray
      Dim targetRange As Range
      Dim x As Long
  
      'Update these 4 lines if your target and search ranges change
      Dim TargetSheetName As String: TargetSheetName = "Formatted"
      Dim TargetSheetColumn As String: TargetSheetColumn = "G22"
      Dim SearchSheetName As String: SearchSheetName = "Overlay"
      Dim SearchSheetColumn As String: SearchSheetColumn = "G22"
  
  
  
      'open Overlay workbook
      Set overLayWB = Workbooks.Open("C:\Documents\Templates\Overlaye.xls") 'Path for workbook Overlay to copy from
      Set formattedWS = Workbooks("Formatted").Sheets("DLT Formatted")
      Set overLayWS = Workbooks("Overlay").Sheets("Overlay")
      Set formattedWB = ThisWorkbook
  
   'Load target array
      With formattedWS
          Set targetRange = .Range(.Range(TargetSheetColumn &amp; "7"), _
                  .Range(TargetSheetColumn &amp; Rows.Count).End(xlUp))
          targetArray = targetRange
      End With
  
   'Load Search Array
      With overLayWS
          searchArray = .Range(.Range(SearchSheetColumn &amp; "7"), _
                  .Range(SearchSheetColumn &amp; Rows.Count).End(xlUp))
      End With
  
  
      Dim dict As Object
      Set dict = CreateObject("Scripting.Dictionary")
      'Populate dictionary from search array
      If IsArray(searchArray) Then
          For x = 1 To UBound(searchArray)
              If Not dict.exists(searchArray(x, 1)) Then
                  dict.add searchArray(x, 1), 1
              End If
          Next
      Else
          If Not dict.exists(searchArray) Then
              dict.add searchArray, 1
          End If
      End If
  
      'Delete rows with values found in dictionary
      If IsArray(targetArray) Then
          'Step backwards to avoid deleting the wrong rows.
          For x = UBound(targetArray) To 1 Step -1
              If dict.exists(targetArray(x, 1)) Then
                  targetRange.Cells(x).EntireRow.Delete
              End If
          Next
      Else
          If dict.exists(targetArray) Then
              targetRange.EntireRow.Delete
          End If
      End If
  </code></pre>
  
  <p>Can anyone help me with this it would be much appreicated, I have not altered the scripting correctly, or is it missing something?</p>