Looping macro to work on all columns individually without writing long list of repeated transitions

Question

I am trying to loop this macro (below) to go through all me data columns but need some help creating a loop or adjusting the current code to work for all columns.

Sub Trial_5()

'
    ActiveCell.Offset(0, -7).Columns("A:A").EntireColumn.Select
    ActiveWorkbook.Worksheets("Sheet6").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet6").Sort.SortFields.Add Key:=ActiveCell, _
        SortOn:=xlSortOnValues, Order:=xlDescending,    DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet6").Sort
        .SetRange ActiveCell.Range("A1:A16395")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        `enter code here`.SortMethod = xlPinYin
       .Apply
    End With
End Sub

do I adjust the .columns or the activecell.offset to total range??

adjusted script

Sub eachcolumndesending()
'
' eachcolumndesending Macro
' descending
'

'
    Columns("A:A").Select
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending,    DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet5").Sort
        .SetRange Range("A2:A32")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:B").Select
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add Key:=Range("B1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet5").Sort
        .SetRange Range("B1:B33")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

You want to sort each column in the used range independently of each of the other columns? — , Mar 17 '17 at 21:40
yes exactly I have a bunch of jumbled data in each columns and I want to arrange them in order with out affecting the additional columns each time I sort descend — cbreezy, Mar 17 '17 at 21:44

score 2 · Answer 1 · 2017-03-17T21:57:19.520

2

I suggest you move away from the recorded code to what VBA actually requires for a single column sort.

Sub sortAllColumns()
    Dim c As Long

    On Error Resume Next  '<~~ may be necessary if a column breaks the sort 
    With Worksheets("Sheet5")
        For c = .UsedRange.Columns.Count To 1 Step -1
            With .Columns(c)
                .Cells.Sort Key1:=.Columns(1), Order1:=xlDescending, _
                            Orientation:=xlTopToBottom, Header:=xlGuess
            End With
        Next c
    End With
End Sub

Btw, you probably shouldn't have to xlGuess at the existence of a header. Either one is there (xlYes) or not (xlNo) but you know your data better than I.

edited Mar 17 '17 at 21:57

answered Mar 17 '17 at 21:50

You are a life savor thanks. I used the recorder code because thats what I have used to learn from this past week. Now I will try to figure our the actual vba scripting since it looks so much simpler lol – cbreezy Mar 17 '17 at 22:14
2

@cbreezy - A great place to start after using the Macro Recorder, is to learn [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), and then when you have a grasp on that, look into the [various Loops in VBA](http://www.excel-easy.com/vba/loop.html). – BruceWayne Mar 17 '17 at 22:29
@BruceWayne Thanks for the help. I'll check out those threads. definiteley need my to learn how to speed up my scripting – cbreezy Mar 17 '17 at 22:58
@cbreezy it's kind of misleading how helpful it is to avoid using `Select` as almost immediately you'll see that using variables is wayyyy helpful, and also avoiding "magic numbers". I still have lots to learn but if you start there it'll be a great start. Even make up simple things to try and do in Excel and you'll quickly grow your skills :D – BruceWayne Mar 18 '17 at 00:27

Looping macro to work on all columns individually without writing long list of repeated transitions

1 Answers1