Try:
Sub SortCols()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim LastPrice As Range
With Sheets("Q13584_10_21_16_PricingTable").Rows(1)
Set LastPrice = .Find(What:="Price Vol. #*", _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With
Dim LastColumn As Long
LastColumn = LastPrice.Column
Dim lastColLetter As String
lastColLetter = Replace(Cells(1, LastColumn).Address(False, False), "1", "")
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("A2:A" & LastRow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(lastColLetter & "2:" & lastColLetter & LastRow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:" & lastColLetter & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub
Re-save the file as a macro-enabled Excel workbook so its extension changes to "xlsm".
Bookmarks