Reputation: 1311
I am trying to write code where somebody can enter the list of column names by which data should be sorted:
Data for sorting is dynamic range located on same sheet:
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
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
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