Sort Columns Individually

**esoterick** · 09-06-2011, 10:46 AM

I'm thinking there's a simple solution to this but I keep hitting a brick wall.

For simplicity's sake, I'll explain the first step:

I have data (text) in column A and either True or False in column B (based on an IF statement). I need to sort column A and column B alphabetically based on the value of column B. This is easy enough, but I then need to do the same thing for columns C and D, then E and F, then G and H, etc.

Is there a way to record a macro where I sort columns A and B the way I want them to be sorted, and then tell Excel to do the same thing to the next two columns, and the next two columns, and the next two columns......

Any help would be GREATLY appreciated - I've been at this for the past two hours and am nowhere near sorting all the way to columns FFA and FFB.

**PaulG2011** · 09-06-2011, 11:07 AM

Hi esoterick,

If you record a new macro, Excel will create cod for specific range of cells that you selected when you did the "demo". All you have to do afterwards is edit the code to make it more generic and youre away.

Below is some demo macro that you might be able to edit.

1) Open visual basic editor by going tools ---> Macro ---> Visual basic editor. Copy and paste the code below into a new module (Insert ---> Module)

2) The macro assumes that the data to be sorted starts in row 4 for all columns and ends before row 100 in all columns, you might need to change this.

3) I have assumed that your data starts in column 1 ( = "A") to be sorted and ends in column 199 (whatever that is!), and it is to be sorted in pairs as per your post.

4) You then look up the reference cells for your selection which are the top-left-most cell and the bottom-right most cell in the pair of columns and get their address, and get the top-right-most cell address because this sets the column you are going to sort by.

5) Your sheet needs to be called "Sheet1" and if it is not you need to replace all instances of sheet1 in the code below.

6) I have done a sort based on the second collumn in each pair, ascending assuming their is no header row. You might need to replace the line starting with "Selection.Sort K...." etc with whatever your recorded macro gives.

7) To run the macro close the visual basic editor and go Tools ---> Macro ---> Macros... Then pick "sortstuff" from the list and click run, and you should be in business.

Id make sure you save the workbook before running the code, just in case is screws stuff up!

Sub sortstuff()

    startrow = 4
    endrow = 100
    
    For i = 1 To 199 Step 2
    
        startcell = Worksheets("Sheet1").Cells(startrow, i).Address
        endcell = Worksheets("Sheet1").Cells(endrow, i + 1).Address
        sortcell = Worksheets("Sheet1").Cells(startrow, i + 1).Address
    
        Worksheets("Sheet1").Range(startcell & ":" & endcell).Select
        Selection.Sort Key1:=Worksheets("Sheet1").Range(sortcell), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        
    Next i
    
End Sub

Good luck,

P

**esoterick** · 09-06-2011, 12:09 PM

Thank you so much P - I really appreciate your help!

I followed your instructions as closely as I could but I'm afraid I'm not there yet in my VB education.

I was receiving errors having to the with Next not having a For or something like that. I don't want to my absence of skill to take up too much of your time but just in case you were feeling even more helpful today than you already have been, I've attached a sample of the spreadsheet I'm working with. :-)

This the code generated form my "demo" macro:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+d
'
    Range("EO2:EP2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range( _
        "EP2:EP802"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("EO2:EP802")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

**WasWodge** · 09-06-2011, 04:47 PM

Hi esoterick, I tried running Paul's code on your sample (I did change the start row to 1 and the end row to 1000) and it ran with no error and gave the results attached. I have left the macro in the sheet to see if that helps you