Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata
Public Sub SortTheData()
 Call FilterWorksheet(ActiveSheet)
 lRow = Get_lRow(ActiveSheet)
 Depot = FindHeaderCol("1:1", "Depot")
 Area = FindHeaderCol("1:1", "Area")
 Location = FindHeaderCol("1:1", "Location")
 Pallet = FindHeaderCol("1:1", "Pallet")
 Workorder = FindHeaderCol("1:1", "Workorder")
 Box = FindHeaderCol("1:1", "Box")
 With ActiveSheet.AutoFilter.Sort
  .SortFields.Clear
  .SortFields.Add Key:=Range(Depot & "2:" & Depot & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SortFields.Add Key:=Range(Area & "2:" & Area & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SortFields.Add Key:=Range(Location & "2:" & Location & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SortFields.Add Key:=Range(Pallet & "2:" & Pallet & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SortFields.Add Key:=Range(Workorder & "2:" & Workorder & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SortFields.Add Key:=Range(Box & "2:" & Box & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
 End With
End Sub

Sort Sheet by Column B

Note: This has issues like only 2 or three keys max I think. See updated method using Sort.

Dim lRow As Long
lRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:D" & lRow ).Sort key1:=Range("B2:B" & lRow ), order1:=xlAscending, Header:=xlNo ' Sort A2:DLRow by Column B

https://stackoverflow.com/questions/21451458/vba-excel-sort-range-by-specific-column

  • No labels