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…

Accepted Answer

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

Excel VBA: Move items from one array to another array

Solution

Problem