Moving element contents of a 2 dimensional array, either up or down

[Deutz]

Hi and thanks in advance,

I am working with a 2 dimensional array in Excel that stores row numbers and names:

E.g.

Row numbers	Names
1	John
2	Peter
3	Fred
4	Tony

I am looking for a way in VBA to move just one of the names, either up or down the array, leaving the row numbers as they are. (I do not need to resize the array):


So, if I wanted Fred to move up to the second element, and move Peter down one, this is how it should look:

Row numbers	Names
1	John
2	Fred
3	Peter
4	Tony

Or if I wanted John to move down to the fourth element, and move Peter, Fred and Tony up one, then this is how it should look:

Row numbers	Names
1	Peter
2	Fred
3	Tony
4	John

Thanks kindly

 

[StephenCrump]

How about:

	A	B	C	D	E
1	IN			OUT
2	1	John		1	Peter
3	2	Peter		2	Fred
4	3	Fred		3	Tony
5	4	Tony		4	John
Sheet1

Sub Test()

    Dim MyArray As Variant
    
    MyArray = Range("A2:B5").Value
    
    Call MoveElement(MyArray, 2, 1, 4)  'John to 4th
    Range("D2:E5").Value = MyArray
    
End Sub
Sub MoveElement(a As Variant, col As Long, From As Long, Too As Long)

    Dim temp As Variant
    Dim i As Long, s As Long
    
    s = IIf(From > Too, -1, 1)
    temp = a(From, col)
    
    For i = From To Too - s Step s
        a(i, col) = a(i + s, col)
    Next i
    a(Too, col) = temp

End Sub

 

[Deutz]

Thanks Stephen for your prompt solution that works as required. Much appreciated.

I see you assign the range to the array directly, withouth looping ... MyArray = Range("A2:B5").Value

I have only the names in the actual project worksheet column and need to add the row numbers as well to the array. Just wondering if there is an easy way to perhaps loop through the rows and add the row numbers along with the names to the 2 dim array?

 

[StephenCrump]

There will be a few ways you could do this. Here's one:

	A	B	C	D
1	IN		OUT
2	John		1	John
3	Peter		2	Peter
4	Fred		3	Fred
5	Tony		4	Tony
Sheet1

Sub Test()

    Dim MyArray As Variant

    With Range("A2:A5")
        MyArray = Evaluate("=HSTACK(SEQUENCE(" & .Rows.Count & ")," & .Address & ")")
        .Offset(, 2).Resize(.Rows.Count, 2).Value = MyArray     'Just to show MyArray
    End With

End Sub

But why store the row numbers? If you're looking at MyArray(i,j), you know the row number is i without having to also store i in the array.

 

[Deutz]

There will be a few ways you could do this. Here's one:

	A	B	C	D
1	IN		OUT
2	John		1	John
3	Peter		2	Peter
4	Fred		3	Fred
5	Tony		4	Tony
Sheet1

Sub Test()

    Dim MyArray As Variant

    With Range("A2:A5")
        MyArray = Evaluate("=HSTACK(SEQUENCE(" & .Rows.Count & ")," & .Address & ")")
        .Offset(, 2).Resize(.Rows.Count, 2).Value = MyArray     'Just to show MyArray
    End With

End Sub

But why store the row numbers? If you're looking at MyArray(i,j), you know the row number is i without having to also store i in the array.

Thanks again Stephen. That is a very neat way to do it.

Yes, you are right, I did not think of that about the row numbers.

Kind regards
Deutz