CODEWITHSUNDEEP
excelvba
Papa
Papa

Reputation: 1

Sorting data in columns with variable number of columns

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.

Upvotes: 0

Views: 342

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

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

Upvotes: 2

Related Questions