title: Excel VBA Store row numbers in Array and delete multiple rows at once
body: <p>I'm trying to delete all rows on my worksheet that have a unique value in <code>column B</code>.  </p>
  
  <p>I know this can be done with a filter or conditioned formatting, but I would like to know if the following is possible as well, since it could be useful in other situations:</p>
  
  <p>I want to loop through all rows and store the row number in an <code>Array</code> if the row has a unique value in <code>column B</code>. Then delete all the rows whose number is stored in the <code>Array</code> in one single action.  </p>
  
  <p>The reasoning for storing the row numbers in an <code>Array</code> instead of deleting the desired rows in the loop is to reduce runtime.</p>
  
  <p>My data varies in number of rows but is always in <code>column A:K</code> and it always begins on row 6.</p>
  
  <p>Below is the code I've written with inspiration from the following links:<br>
  <a href="https://www.thespreadsheetguru.com/blog/dynamically-populating-array-vba-variables" rel="nofollow noreferrer">Dynamically adding values to the array on the go</a>.<br>
  <a href="https://stackoverflow.com/questions/12716111/delete-multiple-rows-in-excel-using-vba-based-on-an-array-of-numbers">Deleting rows whose number is stored in array in one single action</a> (see Tim Williams answer).  </p>
  
  <p>I get the error message: <code>Run-time error '5': Invalid procedure call or Argument</code> </p>
  
  <pre><code>Sub DeleteRows()
  Dim ws4 As Worksheet: Set ws4 = Worksheets("Sheet1")
  Dim LastRow As Long
  Dim CurrentRow As Long
  Dim GroupValue
  Dim GroupTotal As Long
  Dim MyArray()
  Dim y As Long
  
  Application.ScreenUpdating = False
  ws4.Activate
  
  GroupValue = ws4.Range("B6").Value ' Sets the first GroupValue
  CurrentRow = 6 ' Sets the starting row
  y = 0
  LastRow = ws4.Cells(Rows.Count, "B").End(xlUp).Row
  
      For x = 1 To LastRow
  
          GroupTotal=Application.WorksheetFunction.CountIf(Range("B6:B"&amp;LastRow), _
              GroupValue) ' Searches for the GroupValue and finds number of matches
          If GroupTotal = 1 Then ' If GroupTotal = 1 then add the row# to the array
              ReDim Preserve MyArray(y)
              MyArray(y) = CurrentRow
              y = y + 1
          End If
  
          CurrentRow = CurrentRow + GroupTotal 'set the next row to work with
          GroupValue = Range("B" &amp; CurrentRow).Value 'set next GroupValue to find
  
          If GroupValue = "" Then ' Checks to see if the loop can stop
              Exit For
          End If
  
      Next x
  
  '***This should delete all the desired rows but instead produces the error.***
  ws4.Range("B" &amp; Join(MyArray, ",B")).EntireRow.Delete
  Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>I've researched for hours and tried to manipulate the code with no luck.</p>