VBA to sort columns based on assigned sequence

[General Ledger]

Dear All,

I am looking for VBA that will sort columns based on values I assign to labels of the columns. I use this on data where I want columns to be in a certain order.


I have code that:

1. Inserts a blank row above the column labels
2. Puts a value in the blank cell above the column label
3. Sorts the columns based on the values
4. Deletes the row of values

'Insert row in which to enter sequence numbers
    Rows("1:1").Insert Shift:=xlDown
 
'Assign sequence numbers to columns
    Range("A2").Select
    Do Until ActiveCell = Empty
        Select Case ActiveCell.Value
            Case "Supplier"
                ActiveCell.Offset(-1, 0) = 10
                ActiveCell.Offset(0, 1).Select
            Case "Invoice Num"
                ActiveCell.Offset(-1, 0) = 20
                ActiveCell.Offset(0, 1).Select
            Case "Invoice Amount"
                ActiveCell.Offset(-1, 0) = 30
                ActiveCell.Offset(0, 1).Select
            Case Else
                ActiveCell.EntireColumn.Delete
        End Select
    Loop
 
'Sort columns
    Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
 
'Delete sequence number row
    Rows("1:1").Delete Shift:=xlUp

This code can become quite long if I have many columns of data. It seems like there should be an easier way. Any ideas? Maybe something with Arrays?

Thanks,

GL

 

[Ruddles]

You can sort a worksheet from left to right in Excel 2007, and if you combine that with a custom sort list - the Order drop-down list in the Sort dialog box - that should do what you want. If you record yourself carrying out the process, the recorded macro should be fairly simple to tweak.

 

[General Ledger]

Ruddles,

Sorting by a Custom List is an excellent idea and one I would have never thought of.

For my exact situation, I can't have the macro depend on "outside help" such as a Custom List. All the logic needs to be in the macro.


Thinking about arrays, I wonder if something like this might work:

    For Each Word In Array("Supplier, "Invoice Number", "Invoice Amount")
        Set aRange = Rows("1:1").Find(What:=Word, LookAt:=xlWhole, MatchCase:=False)
        If Not aRange Is Nothing Then
 
[I]       code to associate the found Word with the position of the Word[/I]
[I]       in the For Each array [/I]
 
[I]       Supplier is 1 because it is first in the array, [/I]
[I]       Invoice Number is 2 because it is second in the array[/I]
[I]       Invoice Amount is 3 because it is third in the array[/I]
 
        End If
    Next Word
 
[I]code to sort the columns by the array values[/I]

Can anyone offer code or ideas?

 

[Ruddles]

I can't have the macro depend on "outside help" such as a Custom List. All the logic needs to be in the macro.

You can build the custom list in VBA, use it for the sort, and then delete it.