CODEWITHSUNDEEP
arraysvbasortingexcel
TheFullResolution
TheFullResolution

Reputation: 1311

Sorting Dynamic Range by Columns Stored in Array as Variables

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...

Upvotes: 1

Views: 523

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

PaulG
PaulG

Reputation: 1199

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

Upvotes: 1

Related Questions