Excel VBA: Move items from one array to another array

Question

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 & paste.

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.

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:

Updated sample

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.

        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

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.

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

original post, out-of-date

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.

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

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:

vTargetArray(addedIndex) = vSourceArray(searchIndex)

The second thing which does not appear to be easy is deleting a key, but I haven't investigates web resources that much yet.

I would very much appreciate if you could show me the light. Thanks in advance, Stefan

Answer 1

We don't need a temp array to do the combining but since you are using a temp array vRow, let me also use one to illustrate how it works :) See this example

Sub Sample()
    Dim Ar1(), Ar2(), Ar3()
    Dim i As Integer

    Ar1() = Array("A", "B", "C", "D")
    Ar2() = Array("1", "2", "3", "4")

    ReDim Preserve Ar3(1)

    Ar3(1) = Ar1(1)

    'Debug.Print "Ar3 >> "; Ar3(1)

    ReDim Preserve Ar2(UBound(Ar2) + 1)

    Ar2(UBound(Ar2)) = Ar3(1)

    For i = 0 To UBound(Ar2)
        Debug.Print "Ar2 >> "; Ar2(i)
    Next i
End Sub

HTH

FOLLOW UP

If you'd like to have a go, you could put some data in e.g. Sheet1 A1:E5, and A6:E8 or so, and create vSourceArray = range("A1:E5").Value2 and vTargetArray() = Range("A6:E8").Value2 and try to move data in between. That gives you similar arrays to work with as I have them. – ExternalUse 1 hour ago

I did as you suggested but took a slightly different way to achieve what you want. Also for testing purpose, as commented in the code below I have taken lSearchKey as 2

CODE:

Option Explicit

Sub Sample()
    Dim Ar1() As String, Ar2() As String, Ar3() As String
    Dim Rng1 As Range, Rng2 As Range
    Dim ws As Worksheet
    Dim i As Long, j As Long

    Set ws = Sheets("Sheet1")

    With ws
        Set Rng1 = .Range("A1:E5")
        Set Rng2 = .Range("A6:E8")

        '~~> Redim Ar2 and Ar3 arrays
        ReDim Ar2(Rng2.Rows.Count, Rng2.Columns.Count)
        ReDim Ar3(0, Rng2.Columns.Count)

        '~~> Store Range 2 in Ar2
        For i = 0 To Rng2.Rows.Count - 1
            For j = 0 To Rng2.Columns.Count - 1
                Ar2(i, j) = Rng2.Cells(i + 1, j + 1)
                'Debug.Print Ar2(i, j)
            Next j
        Next i

        '~~> Manually setting the Search Key for testing purpose
        Dim lSearchKey As Long
        lSearchKey = 2

        '~~> Adding the relevant data from Ar2 to Ar3
        For i = 0 To Rng2.Columns.Count - 1
            Ar3(0, i) = Ar2(lSearchKey - 1, i)
            'Debug.Print Ar3(1, i)
        Next

        '~~> Redim the 1st Array
        ReDim Preserve Ar1(Rng1.Rows.Count, Rng1.Columns.Count)

        '~~> Store Range 1 in Ar1
        For i = 0 To Rng1.Rows.Count - 1
            For j = 0 To Rng1.Columns.Count - 1
                Ar1(i, j) = Rng1.Cells(i + 1, j + 1)
                'Debug.Print Ar1(i, j)
            Next j
        Next i

        '~~> Store the Ar3 into Ar1
        For i = 0 To Rng2.Columns.Count - 1
            Ar1(UBound(Ar1), i) = Ar3(0, i)
            Debug.Print ">>"; Ar1(UBound(Ar1), i)
        Next i
    End With
End Sub

SNAPSHOT