, comma issue for Sorting trying to get around. Any Help

[zone709]

Hi anyway im using a sorting code to sort my stuff. So I have a bunch of names to sort in order. How and if I can get around what I colored in red. I cant get it to be in order cause in the wording I have a couple of comma's in there. Is there a way to make that part read as one area other then picking it up as comma's. Thought maybe something like this, but this is wrong "Concrete Laborer - Local 6, 18A, 20"

SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _ ,Line 4,Carp 151,Concrete 151,[COLOR="#FF0000"]Concrete Laborer - Local 6, 18A, 20[/COLOR],Concrete Laborer Foreman - Local 6,", DataOption:= _
 xlSortNormal

Concrete Laborer - Local 6 18A 20 <--- This is I one tab for me but I need the comma's in there

 

[oldbrewer]

what is in the columns you are trying to sort, please ?

 

[wideboydixon]

I think you'll find your answer here:

VBA Type Mismatch on CustomOrder - Stack Overflow

WBD

 

[zone709]

im trying to sort this "Line 4,Carp 151,Concrete 151,Concrete Laborer - Local 6, 18A, 20,Concrete Laborer Foreman - Local 6,"

I underlined what I wanted sorted also, but it wont sort that full phrase I underlined cause that statement has comma's in it

 

[zone709]

Full code:

Sub Macro1()
 Dim rng1 As String
 Dim rng2 As String

 ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C4:C" & CStr(Cells.Find("MASONS", LookIn:=xlValues, LookAt:=xlWhole).Row - 1)), _
 SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
 "Line 4,Carp 151,Concrete 151,Concrete Laborer - Local 6, 18A, 20,Concrete Laborer Foreman - Local 6,", DataOption:= _   
 xlSortNormal
 With ActiveWorkbook.Worksheets("Data").Sort
 .SetRange Range("B4:AH" & Cells.Find("MASONS", LookIn:=xlValues, LookAt:=xlWhole).Row - 1)
 .Header = xlGuess
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
 End Sub

Can't get this line in the order I want it puts it to the end cause I have comma's in it "Concrete Laborer - Local 6, 18A, 20''

 

[wideboydixon]

Example from the link I posted:

Sub Macro1()

Dim rng1 As String
Dim rng2 As String

Dim keyRange As Variant
Dim sortNum As Long

keyRange = Array("Line 4", "Carp 151", "Concrete 151", "Concrete Laborer - Local 6, 18A, 20", "Concrete Laborer Foreman - Local 6")
Application.AddCustomList ListArray:=keyRange
sortNum = Application.CustomListCount

ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C4:C" & CStr(Cells.Find("MASONS", LookIn:=xlValues, LookAt:=xlWhole).Row - 1)), _
    SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=sortNum, DataOption:=xlSortNormal
    
With ActiveWorkbook.Worksheets("Data").Sort
    .SetRange Range("B4:AH" & Cells.Find("MASONS", LookIn:=xlValues, LookAt:=xlWhole).Row - 1)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

WBD

 

[zone709]

ok I see you have to use Key range Array to use "...." gonna try this now ill let you know thanks for the response

 

[zone709]

wideboydixon This works great thanks for the help. Just got done testing it. Thank you also oldbrewer

 

[oldbrewer]

WBD - are we sorting cells by only a designated part of the cell contents

eg cell contains big blue bus
and we want to sort only by color order ?

thanks

 

[zone709]

Its doing what I thin its suppose to do oldbrewer. I have it sorting starting at line 4 B to AH downward one row before the word masons