Key:= Range help

[zone709]

Hi this works fine right, but I'm trying to change the range as my sheet extended a little. So I don't have to keep changing it I want to change range to

C4:C B4:B & B4:AH

if I change it all it breaks at each spot range above. I think there is something small I'm missing and cant figure it out. Thanks

Sub Sorting1224()

Dim keyRange As Variant
Dim sortNum As Long

keyRange = Array("Office", "Field Intern", "Trainer", "Supervisor", "Assistant", "Safety", "Super", "Super - Local 15D", "Assistant Field Engineer")
Application.AddCustomList ListArray:=keyRange
sortNum = Application.CustomListCount
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("[COLOR=#b22222]C4:C200[/COLOR]"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=sortNum, DataOption:=xlSortNormal

 ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("[COLOR=#b22222]B4:B200[/COLOR]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("[COLOR=#b22222]B4:AH200[/COLOR]")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4").Select
End Sub

 

[shg]

How about ...

Sub Sorting1224()
  Dim nList         As Long
  Dim wks           As Worksheet

  Application.AddCustomList ListArray:=Array("Office", "Field Intern", "Trainer", "Supervisor", "Assistant", "Safety", "Super", "Super - Local 15D", "Assistant Field Engineer")
  nList = Application.CustomListCount

  Set wks = Worksheets("Data")

  With wks.Sort
    .SortFields.Clear
    .SortFields.Add Key:=wks.Range("C4"), CustomOrder:=nList
    .SortFields.Add Key:=wks.Range("B4")
    .SetRange wks.Range("B4", wks.Cells(Rows.Count, "AH"))
    .Header = xlYes  ' (or xlNo; why guess?)
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
  End With

  Application.DeleteCustomList nList
End Sub

 

[zone709]

Hi ok this works great. I don't know why the guess part. I think when I first recorded part of this awhile back it was added. I didn't add it at all. Not sure and didn't really look into it. I do have header on sheet from row 1 to 3 why I'm starting at all 4's

 

[shg]

You're welcome.

 

[zone709]

​Thanks