, 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

 

[zone709]

Hi again, one question when I use this code right then save iti ts Fine, but when I open it says it has to repair it.

It says we found a problem with some content in sorted.xlsm Do you want us to try to recover as much as we can? If you trust the source of this workbook click yes.

Then
after the repair it says: Excel was able to open the file by repairing or removing the unreadable content.

Removed Records: Sorting from /xl/worksheets/sheet1.xml part

Do you think whats causing this? thanks

 

[wideboydixon]

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

It's sorting by using a completely custom order. Excel allows you to specify how to sort data in a column so I could, for example, set a custom sort order that is "Fish", "Banana", "10", "Wednesday", "Aardvark" and it would sort the data accordingly so that "Fish" always appears first.

WBD

 

[wideboydixon]

Hi again, one question when I use this code right then save iti ts Fine, but when I open it says it has to repair it.

It says we found a problem with some content in sorted.xlsm Do you want us to try to recover as much as we can? If you trust the source of this workbook click yes.

Then
after the repair it says: Excel was able to open the file by repairing or removing the unreadable content.

Removed Records: Sorting from /xl/worksheets/sheet1.xml part

Do you think whats causing this? thanks

I'm afraid I've no idea why it needs to repair it unless there's a problem with Excel.

WBD

 

[zone709]

I'm afraid I've no idea why it needs to repair it unless there's a problem with Excel.

Thinking maybe if i go into sort and manually put it in the order i have it in the code. Then run the code i wont get error. i think maybe its adding it on its own the order i want and maybe giving that error. idk

 

[oldbrewer]

WBD - I know custom sorts but is OP only sorting on part of a cell ?

 

[zone709]

What do you mean OP?

 

[wideboydixon]

What do you mean OP?

OP = Original Poster

 

[zone709]

So this runs fine right and it saves fine my sheet after its done, but when I open it it wants to repair and says I could of lost readable content. I just don't know why.

This is my error message after I open it.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error071400_01.xml</logFileName><summary>Errors were detected in file 'C:\Timesheets\2016-Jul\Jul-29\07-29-2016 Timesheet Sorted.xlsm'</summary><removedRecords><removedRecord>Removed Records: Sorting from /xl/worksheets/sheet1.xml part</removedRecord></removedRecords></recoveryLog>

Sub Sort2Use()

Dim rng1 As String
Dim rng2 As String

Dim keyRange As Variant
Dim sortNum As Long

keyRange = Array("Office", "Assistant Field Engineer", "Operator - Local 14", "Pump Operator - Local 14", "Hoist-Local 14", "Crane Operator - Local 14", "Operator - Local 15")
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