Sorting data in columns with variable number of columns

Question

I haven't used VBA for a while and I am very rusty. I have an Excel Worksheet with data in several columns. For example now I have data in columns A to E. All rows in column A are full, but some cells can be empty for the next columns. I am trying to write a macro that will sort automatically data in the following order: Sort data in column A, then sort data in column B, then column C, etc. I want the macro to be adaptive and still be able to sort the data if data are added into the next column (F in this case).

I have tried different solutions found online, but none of them worked because they all imply a predefined number of columns.I recorded a macro and try to adapt it, but I cannot see how I can make it flexible enough so it would see a new column. I have a macro that can give me the last column and the last row containing data. I am trying to use this so I can tell my sorting macro how many columns need to be processed, but I fail to see how to implement it. Here is what I've got from recording the macro:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add 
Key:=Range("A1:F80") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, 
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add 
Key:=Range("B1:B80") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, 
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add 
Key:=Range("C1:C80") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, 
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add 
Key:=Range("D1:D80") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, 
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add 
Key:=Range("E1:E80") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, 
DataOption:=xlSortNormal

This would not work because it implies I already know how many columns I have. The only way to process an extra column would be by adding a line of code (for column F in this case) as the macro is running. Is that possible? Is there a better/different method offering more flexibility? Thank you.

tigeravatar · Accepted Answer · 2019-05-09 19:43:33Z

2

Loop through the columns of your sort range to add them to the sort order, like so:

Sub tgr()

    Dim rSort As Range
    Dim i As Long

    Set rSort = ActiveWorkbook.ActiveSheet.Range("A1").CurrentRegion

    With rSort.Parent.Sort
        .SortFields.Clear
        For i = 1 To rSort.Columns.Count
            .SortFields.Add rSort.Columns(i), xlSortOnValues, xlAscending, , xlSortNormal
        Next i
        .SetRange rSort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

answered May 9, 2019 at 19:43

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Papa Over a year ago

It is working perfectly, thank you. I've never used the CurrentRegion property, that will be a useful one.

Collectives™ on Stack Overflow

Sorting data in columns with variable number of columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related