title: Excel VBA: Move items from one array to another array
body: <p>I'm trying to speed up an application that is designed to assign human resources to locations, using listboxes that are bound to ranges. That works quite well - the ugly part is moving items from one data range to one or more ranges using find, copy &amp; paste.</p>
  
  <p>I could gain great speed by using a function to print arrays to ranges when I retrieve the data from webservices, but I couldn't figure out how to replace the find/cut/paste logic yet.</p>
  
  <p>I have now updated my previous post to include my latest tries. In a way that now works as intended, but it surely does not look smart:</p>
  
  <h3>Updated sample</h3>
  
  <p>The ranges look like this (data in Col B-E is not relevant, A contains the key).
      Day0_lbUsers is A1:E5, Day1_lbUsers is A28:E30.</p>
  
  <pre><code>        A       B       C       D       E
  1       15      Foo     Bar     Bas     Nono
  2       18      Foo     Bar     Bas     Nono
  3       19      Foo     Bar     Bas     Nono
  4       196     Foo     Bar     Bas     Nono
  5       33      Foo     Bar     Bas     Nono
  ...
  28      32      Foo     Bar     Bas     Nono
  29      46      Foo     Bar     Bas     Nono
  30      52      Foo     Bar     Bas     Nono
  </code></pre>
  
  <p>In this example, I want to move the row with the key 18 from Day0_lbUsers to Day1_lbUsers.
  In the sample, I have hardcoded the source and not written back to the ranges, but that's not the hard part. I'm rather interested whether there is a better way to transfer the arrays contents.</p>
  
  <pre><code>Sub TestRemoveFromArray()
      Dim vSourceArray() As Variant ' source
      Dim vNewSourceArray() As Variant ' source, one key removed
      Dim vTargetArray() As Variant ' target
      Dim vNewTargetArray() As Variant ' target, one item added
      Dim rowSearch As Long, row As Long, col As Long, search As Long, blnFound As Boolean
      search = 18
      vSourceArray = shData.Names("Day0_lbUsers").RefersToRange.Value2 ' 27 rows, 5 columns, key in col 1
  
      ' loop source to find the row that contains the search key
      For rowSearch = LBound(vSourceArray) To UBound(vSourceArray)
          ' look into col 1 for the key
          If vSourceArray(rowSearch, 1) = search Then
              blnFound = True
              Exit For
          End If
      Next rowSearch
  
      If Not blnFound Then
          Exit Sub
      End If
      ' we've found the row, so let's get the target
      vTargetArray = shData.Names("Day1_lbUsers").RefersToRange.Value2
      ' a1 needs to be 1 short of a, b1 must be b +1
      ReDim vNewSourceArray(LBound(vSourceArray) To UBound(vSourceArray) - 1, 1 To 5)
      ReDim vNewTargetArray(LBound(vTargetArray) To UBound(vTargetArray) + 1, 1 To 5)
  
      ' copy original target to new target
      For row = LBound(vTargetArray) To UBound(vTargetArray)
          For col = LBound(vTargetArray, 2) To UBound(vTargetArray, 2)
              vNewTargetArray(row, col) = vTargetArray(row, col)
          Next col
      Next row
      ' reset blnFound
      blnFound = False
      For row = LBound(vSourceArray) To UBound(vSourceArray)
          If row = rowSearch Then
              For col = LBound(vSourceArray, 2) To UBound(vSourceArray, 2)
                  vNewTargetArray(UBound(vNewTargetArray), col) = vSourceArray(row, col)
              Next col
              blnFound = True
          Else
              For col = LBound(vSourceArray, 2) To UBound(vSourceArray, 2)
                  ' if blnFound was found before, write to the key -1
                  vNewSourceArray(IIf(blnFound, row - 1, row), col) = vSourceArray(row, col)
              Next col
          End If
  NextRow:
      Next row
  
      'assign new arrays (return later)
      vSourceArray = vNewSourceArray
      Erase vNewSourceArray
      vTargetArray = vNewTargetArray
      Erase vNewTargetArray
  
  End Sub
  </code></pre>
  
  <h3>original post, out-of-date</h3>
  
  <p>All the data ranges have the same number of columns (5) and are named. This is what I have so far; at some point I had to stop programming and use pseudo-code instead to illustrate. The source and target arrays are created with e.g. </p>
  
  <pre><code>vSourceArray = shData.Names("Day0_A").RefersToRange.Value2 ' (1 to 27, 1 to 5)
  
  Private Function MoveUserId(ByRef vSourceArray() As Variant, ByRef vTargetArray() As Variant, lngUserId As Long) As Boolean
      Dim lSearchKey As Long, blnFound As Boolean, col As Long
      Dim vTempArray() As Variant, vRow() As Variant
      For lSearchKey = LBound(vSourceArray) To UBound(vSourceArray)
          If vSourceArray(lSearchKey, 1) = lngUserId Then
              blnFound = True
              Exit For
          End If
      Next lSearchKey
      If blnFound = False Then
          MoveUserId = False
          Exit Function
      End If
      ' extract the row found
      ReDim vRow(1 To 1) As Variant
      vRow(1) = Application.WorksheetFunction.index(vSourceArray, lSearchKey)
      ' now, add an item to targetarray and populate using a function from http://www.cpearson.com
      vTargetArray = CombineTwoDArrays(vTargetArray, vRow) ' does not work
  
      ' now delete the key in source array
      ' help!  
  End Function
  </code></pre>
  
  <p>Apart from the search function, this does not really work. The first thing would be to extract a row and copy it to a new, re-dimensioned target array. Easiest would be to redim the target to elements + 1; and then do something like (pseudo-code) pushing it to the end:</p>
  
  <pre><code>vTargetArray(addedIndex) = vSourceArray(searchIndex)
  </code></pre>
  
  <p>The second thing which does not appear to be easy is deleting a key, but I haven't investigates web resources that much yet.</p>
  
  <p>I would very much appreciate if you could show me the light.
  Thanks in advance,
  Stefan</p>