Rearrange Columns Within Array
Rearrange Columns Within Array
Rearrange Columns Within Array
Rearrange Columns Within Array
Rearrange Columns Within Array
Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array
Rearrange Columns Within Array Rearrange Columns Within Array
Rearrange Columns Within Array
Go Back  Xtreme Visual Basic Talk > > > > Rearrange Columns Within Array


Reply
 
Thread Tools Display Modes
  #1  
Old 01-29-2017, 12:30 PM
cacody cacody is offline
Junior Contributor
 
Join Date: Apr 2011
Location: Scottsdale AZ
Posts: 323
Default Rearrange Columns Within Array


The "Range to Array back to Range" construct is pretty simple. Example:
Code:
Dim x As Variant

x = Range("A1:C10").Value
Range("D1:F10") = x
I understand the benefits of using arrays instead of copy/pasting are many. The array is a virtual range loaded into memory. Once loaded, you can manipulate the values in the array, then print back to a range and everything is lightning fast.

What I would like to do is to rearrange the columns once loaded into the array, or perhaps load each value to the desired column (same row). From the example above, if I wanted all values in Column C moved to Column B, and values from Column A moved to Column C, then it would look like: Range("B1:B10, C1:C10, A1:A10"). I realize I can create separate arrays for each column, then specify where to print them to the range, but if there is a way to rearrange them from within the array then it would make it easier to code and print to the range. Any ideas?
Reply With Quote
  #2  
Old 01-29-2017, 01:09 PM
Cerian Knight's Avatar
Cerian KnightRearrange Columns Within Array Cerian Knight is offline
Polymath (in disciplina)

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 4,193
Default

You might be able to use the INDIRECT Excel formula.

Or, perhaps, a tracking array in VBA. Thus, none of the columns has to actually move until it efficient to do so (and then the indirect array must be cleared). This presumes the range contains values, as formulas are more difficult to manage.

A VBA example of making columns indirect (assuming the range has already been loaded into the VBA array):
value=A(r,i(c)) 'where array i(c) initially contains the values 1 through MaxColVal. Swap pairs of i(c) elements to swap the columns.

When you are ready put A (with indirects) back into the cells.

Not sure if this will help.
__________________
I got all the answers wrong on the GLAT, apparently even #9 (where I put a period in the middle of the box and labeled it 'singularity ripe for rapid inflation').
Reply With Quote
  #3  
Old 01-30-2017, 08:13 AM
MPiRearrange Columns Within Array MPi is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Dec 2001
Location: Quebec
Posts: 1,000
Default

You may want to rearrange the array in memory like this ?
Code:
Sub SortArray()
    Dim I As Long
    Dim Tmp
    Dim arrValues
    
    arrValues = Range("A1:C10").Value
    
    For I = 1 To UBound(arrValues)
        Tmp = arrValues(I, 1)
        arrValues(I, 1) = arrValues(I, 2)
        arrValues(I, 2) = arrValues(I, 3)
        arrValues(I, 3) = Tmp
    Next
    
    Range("D1:F10") = arrValues
End Sub
__________________
MPi²
Reply With Quote
  #4  
Old 01-30-2017, 01:09 PM
cacody cacody is offline
Junior Contributor
 
Join Date: Apr 2011
Location: Scottsdale AZ
Posts: 323
Default

CK, MPi - thank you!
I promise I'll try to grow into the INDIRECT method but MPi's code works great and will let me get going while I continue to grow my understanding. Thanks again.
Reply With Quote
Reply

Tags
range, column, array, values, loaded, print, rearrange, columns, moved, arrays, ideas, code, easier, create, row, separate, realize, a1a10, rangeb1b10, c1c10, copy/pasting, memory, virtual, ranged1f10, rangea1c10.value


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Rearrange Columns Within Array
Rearrange Columns Within Array
Rearrange Columns Within Array Rearrange Columns Within Array
Rearrange Columns Within Array
Rearrange Columns Within Array
Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array Rearrange Columns Within Array
Rearrange Columns Within Array
Rearrange Columns Within Array
 
Rearrange Columns Within Array
Rearrange Columns Within Array
 
-->