• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reverse using arrays not loops

YasserKhalil

YasserKhalil

Well-Known Member
Hello everyone
I have values (text and numbers) in Range A2:A?
I need to reverse the values in column B ..
For example: A1:A4 has the values (Yasser, 15, Khalil, Hello)
In B1:B4 I need the results to be (Hello, Khalil,15, Yasser)
I need doing it using arrays not loops ..I am searching for the simplist way to do that task
Thanks advanced for help
 
Hi !

For this time, best way could be a loop !

But for a static range :​
Code: 
Sub Demo1()
    VA = [A1:A4].Value
    [B1:B4].Value = Application.Transpose(Array(VA(4, 1), VA(3, 1), VA(2, 1), VA(1, 1)))
End Sub
You may avoid an array variable like VA using directly cells addresses …
​
 
Code: 
Sub Demo2()
    [B1:B4].Value = Evaluate("{""" & [A4].Value & """;""" & [A3].Value & """;""" & [A2].Value & """;""" & [A1].Value & """}")
End Sub
 
Thanks Mr. MarcL for reply
I refer in first post to the range("A1:A?) that is I mean this is dynamic not fixed range. This is point
I tried to achieve it and I did it just now
Code: 
Sub Test()
    Dim Arr, Temp, I As Long, P As Long
    Arr = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    ReDim Temp(UBound(Arr) - 1)
   
    For I = UBound(Arr) To LBound(Arr) Step -1
        Temp(P) = Arr(I, 1)
        P = P + 1
    Next I
   
    Range("B1").Resize(UBound(Arr)).Value = Application.Transpose(Temp)
End Sub
 
Code: 
Sub test()
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Offset(, 1).Value = Application.Index(.Value, Evaluate("if(row(" & .Address & "),large(row(" & .Address & "),row(1:" & .Rows.Count & ")))"))
    End With
End Sub
 
Thank you very much Mr. Jindon for this wonderful solution. I liked it very much
Thanks a lot Mr. Hui for this awesome formula ...
I didn't expect these fascinating solutions for the issue
Best Regards
 
There's a built-in object which can also do this. It is not very fast but has some useful functionalities. Refer:
https://msdn.microsoft.com/en-us/library/system.collections.arraylist(v=vs.110).aspx

Here's code
Code: 
Public Sub InvertUsingArrayList()
Dim objArrList As Object: Set objArrList = CreateObject("System.Collections.ArrayList")
Dim reverseArr
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    objArrList.Add Range("A" & i).Value
Next i
objArrList.Reverse
reverseArr = objArrList.ToArray
Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Value = Application.Transpose(reverseArr)
End Sub
 
jindon said:
Nobody seems to understand the meaning of "No Loop"...
Click to expand...
I do partially;) The reversing part is non looping one. However, ArrayList does not directly accept the Excel Range so I had to resort to that one. Faster would be loading range into an array and then feeding arraylist.
 
Actually Mr. Jindon
There are no loops at all as for your code and that's fascinating ..
But I remember you told me once that loops when dealing with arrays is unremarkable .. And I meant not to use loops (regular loops that depend on the ranges ..)
Regards
 
If you say so, there is a faster way.
Code: 
Sub test()
    Dim a, i As Long, temp
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        For i = 1 To UBound(a, 1) \ 2
            temp = a(UBound(a, 1) - i + 1, 1)
            a(UBound(a, 1) - i + 1, 1) = a(i, 1): a(i, 1) = temp
        Next
        .Offset(, 1).Value = a
    End With
End Sub
 
jindon said:
See my code.

No sort/arraylist.

Absolutely no loop with array.
Click to expand...
Whatever I have posted is in Arraylist context. And I have also mentioned in my initial post that ArrayList is not the fastest. It is just that it has a built-in functionality to reverse element sequence. Hope this clears.
 
You must log in or register to reply here.
Back
Top