1

I am trying to write code where somebody can enter the list of column names by which data should be sorted:

Variables

Data for sorting is dynamic range located on same sheet:

Range Columns

Below is the code I have so far:

updateTab = Sheets("RAW_DATA_SO").Range("B8")

lastRow = Sheets("RAW_DATA_SO").Range("A1048576").End(xlUp).Row

 Dim sortBy() As String
   ReDim sortBy(lastRow - 12)

    For rowNumber = 12 To lastRow

    sortBy(rowNumber - 12) = Sheets("RAW_DATA_SO").Range("A" & rowNumber)

  Next
lastColumnAddress = Sheets("RAW_DATA_SO").Range("XFD1").End(xlToLeft).Address(False, False)
serchrange = "A1:" & lastColumnAddress
Set sortRange = Range(Cells(1, 10), Cells(lastRow, lastColumn))


For i = 0 To UBound(sortBy)

 Set FindColumn = Sheets("RAW_DATA_SO").Range(serchrange).Find(What:=sortBy(i), LookIn:=xlValues, LookAt:=xlWhole)

   sortByColumn = FindColumn.Address(ReferenceStyle)


    sortRange.Sort key1:=Range(sortByColumn), order1:=xlAscending, Header:=xlYes


Next

Problem is data is sorted only one column at time. How I can rewrite the sorting procedure that data is being sorted by more than one column? I found codes where you can add more columns but they are not flexible, there is always an assumption that we we now by how many columns the data will be sorted. I want to make it possible to just added Sort By list...

Improve this question
2
  • You may be interested in this recent post : Sort predetermined fields with a macro. Commented Apr 30, 2015 at 13:37
  • If you are adding in all of this dynamic automation, have you considered using column C (beside B8:B10) for Ascending/Descending? Commented Apr 30, 2015 at 14:00

2 Answers 2

Reset to default
1

Given that VBA can perform a sort of a maximum of three keys at once, it would seem that walking backwards through the listed sort key fields would be best.

Sub dynamic_sort()
    Dim lc As Long, lr As Long, v As Long, k As Long, vKEYs As Variant

    With Sheets("RAW_DATA_SO")
        With .Range(.Cells(12, 1), .Cells(12, 1).End(xlDown))
            vKEYs = .Value2
        End With

        Debug.Print LBound(vKEYs, 1) & ":" & UBound(vKEYs, 1)
        Debug.Print LBound(vKEYs, 2) & ":" & UBound(vKEYs, 2)

        For k = LBound(vKEYs, 1) To UBound(vKEYs, 1)
            Debug.Print vKEYs(k, 1)
        Next k

        lr = .Cells(Rows.Count, 10).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column - 9
        With .Cells(1, 10).Resize(lr, lc)
            For v = UBound(vKEYs, 1) To 1 Step -3
                Select Case v
                    Case Is > 2
                        .Cells.Sort Key1:=.Columns(Application.Match(vKEYs(v - 2, 1), .Rows(1), 0)), Order1:=xlAscending, _
                                    Key2:=.Columns(Application.Match(vKEYs(v - 1, 1), .Rows(1), 0)), Order2:=xlAscending, _
                                    Key3:=.Columns(Application.Match(vKEYs(v, 1), .Rows(1), 0)), Order3:=xlAscending, _
                                    Orientation:=xlTopToBottom, Header:=xlYes
                    Case 2
                        .Cells.Sort Key1:=.Columns(Application.Match(vKEYs(v - 1, 1), .Rows(1), 0)), Order1:=xlAscending, _
                                    Key2:=.Columns(Application.Match(vKEYs(v, 1), .Rows(1), 0)), Order2:=xlAscending, _
                                    Orientation:=xlTopToBottom, Header:=xlYes
                    Case 1
                        .Cells.Sort Key1:=.Columns(Application.Match(vKEYs(v, 1), .Rows(1), 0)), Order1:=xlAscending, _
                                    Orientation:=xlTopToBottom, Header:=xlYes
                End Select
            Next v
        End With

    End With
End Sub

If you have more than three fields to sort on, you need to sort the secondary ones first and then continue sorting as you step toward the primary key.

The Select Case statement offers three options for sorting so that the maximum number of keys is always used.

The best I could gather from your code and sample images was that the SORT BY column label was in 'RAW_DATA_SO'!A11 with the sort keys below that starting in A12. The top left corner of the sorting range was 'RAW_DATA_SO'!J1 and there was a header row for the sort range.

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

6 Comments

Your assumptions about my code are correct. I am still investigating your code, will have some questions probably. However If you say walking backwards through the listed sort key is best, could I just reverse the loop, and say write something like that: instead of "For i = 0 To UBound(sortBy)" ---> "Do (...) i = i - 1 Loop Until i < 0" where i = UBound(sortBy)
@Data.life - That sounds like it would work if you did it one at a time. From your example the keys were Company, Order, Material. Let's add a couple more so the keys are Company, Order, Material, Sales, Record with Company the primary key and Record the lowest priority. First you need to sort on Material (primary), Sales (2nd) and Record (last). Then you sort on Company (primary) and Order (secondary). FWIW, I sorted on 5 fields after duplicating rows of records to force the hierarchy and it worked well. BTW, your back=crawl would be For i = UBound(sortBy) To LBound(sortBy) Step -1.
your code works really nice, I was going trough every procedure and there are lines I don't understand: "Debug.Print" - I see what it does, but how does it work that you assign values to the array via it?
The Debug.Print is for information purposes only. It tells me that I have written the code right and I'm referencing the right cells on the worksheet in the right orientation (e.g. without having to use application.transpose to flip it). Once the code is working the way you want it or you are sure you no longer need them, just delete them.
@Data.life - The number you are referring to is the array's Rank. Best to do a little research for something like vba variant array rank.
|
1

Here's a bit of code I've knocked up very quickly so sort by col name.... but you should get the idea... 

Public Sub SortColumns(ByVal DataTable As Range, ParamArray ColumnNames() As Variant)

    Dim vColName As Variant
    Dim rSortCol As Range

    DataTable.Parent.Sort.SortFields.Clear
    For Each vColName In ColumnNames
        Set rSortCol = FindColumn(DataTable, vColName)
        If Not rSortCol Is Nothing Then _
                DataTable.Parent.Sort.SortFields.Add Key:=rSortCol, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Next

    With DataTable.Parent.Sort
        .SetRange DataTable
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Public Function FindColumn(ByVal DataTable As Range, ByVal ColumnName) As Range

    Dim rPtr As Range, rHeader As Range

    Set rHeader = DataTable.Resize(1)
    Set rPtr = rHeader.Find(ColumnName, rHeader(rHeader.Count), XlFindLookIn.xlValues, XlLookAt.xlWhole)
    If Not rPtr Is Nothing Then Set FindColumn = rPtr.Resize(DataTable.Rows.Count)

End Function
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.