Rearrange colums (2002/SP3)

Topic

New Reply

I have an array that contains a list of names, for example: ORB160, WP_ELEC, S_ELEC, EE

I have a VBA code that first sorts the array in ascending order, then does some data muniplulation, and finally output the results to a worksheet. The output headings contain the array list, for example:

B1 C1 D1 E1

A1 EE ORB160 S_ELEC WP_ELEC

Note that the headings are sorted.

What I want to do is re-arrange the data in each column based on the original list of names in the arrays. In this example, this would be:

B1 C1 D1 E1

A1 ORB160 WP_ELEC S_ELEC EE

So the solution would be to move column E to B, and then move column C to D. However, I’m looking for a more generic VBA to do this since the order and the list of names in the array can change.

Can someone provide me with some help on getting this done?

Reply | Quote

Replies

You could store the original sort order in another array:
1 2 3 4
When you sort the array of names, perform the same steps on this array. It ends up
2 4 3 1
When you’re done, sort this array in ascending order, and perform the same steps on the array of names (or on the columns). In the end, the column headings are back in the original order.

Reply | Quote

The problem is that the array (containing the list of names) and the data (values) in the columns are put together in the worksheet, and not in a VBA array, ie, I don’t store the data in another array. See an example worksheet. I need to re-arrange the columns based on a specific heading list.

Reply | Quote

That shouldn’t matter, but since you haven’t given any details of what you’re doing, I can’t provide more specific help.

Reply | Quote

You might make an Excel>Options>Custom List of the headers in the order that you want.
Then sort (left to right) on that custom list.

Reply | Quote

Attached is the worksheet and the VBA code that would produce the table shown in Sheet1. What I’d like to do is re-order the table based on the order that is specified the file “Nodes_of_Interest.txt”.

Reply | Quote

Any questions/comments?

Reply | Quote

If I understand what you are doing you can try these changes and additions.

Delete the lines as there is no reason to sort the array if you want them in the “node of interest” order:

' Sort array in order to bring together same submodel names
  SortInfo DataArray

At the red lines in the listed spot
This sorts the items horizontally based on a row added for sorting

  ReadTemperatureFromFiles DataArray, strFolder, strFile, f, blnNeedTime
  Range("A1").CurrentRegion.Sort _
    Key1:=Range("A1"), Order1:=xlAscending, _
    Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    Range("a1").EntireRow.Delete
    Cells.EntireColumn.AutoFit
ExitHandler:

Change the line to start 1 row down to allow for sorting row

  R = 2

Add the line for the 0 column for sorting

  blnNeedTitle = True
  ActiveCell.Cells(R - 1, 1) = 0
  ActiveCell.Cells(R + 1, 1) = "Time"

Add and edit the following code to put in the sorting row and put the data 1 row down

                    If blnNeedTitle = True Then
                        ActiveCell.Cells(1, C + 1) = i
                        ActiveCell.Cells(2, C + 1) = Trim(SubModelName) & _
                                            "." & DataArray(i).dblNodeNumber
                        ActiveCell.Cells(3, C + 1) = DataArray(i).strNodeName
                    End If

Steve

Reply | Quote

Great, that’s what I was looking for. Thanks alot Steve.

Reply | Quote