VBA Custom Sort works, mostly............???????

[cizzett]

So I am using the below code to sort based on the parameters in order and it works except the very first status "ETOX" keeps going to the bottom instead of the top.

I can change to descending but then the order is incorrect.

What am I missing here?

Sub SteriSort()' Sort by Steri Macro
 Call Steri_Dec
 
With ST.Sort
        .SortFields.Clear
        .SortFields.Add Key:=SteriS, SortOn:=xlSortOnValues, Order:=xlAscending _
        , CustomOrder:="ETOX, ETOXCNWY, ETOXNMTF, ETOXODFL, ETOXFXFE, ETOXLMEL, ETOXGGJ, ETOXMSP, ETOXREPL" _
        , DataOption:=xlSortNormal
        .Header = xlYes
        .Apply
    End With
End Sub

 

[Joe4]

Are you sure that your entries of "ETOX" are EXACTLY equal to "ETOX", and there aren't any extra spaces in those entries on your sheet?

 

[cizzett]

YOUR RIGHT,

so now to try to figure out how to get a trim to work on a column in a table.

Anyone have a suggestion? been trying to work it for a while, was hoping to set it as a with change event so whenever the data is pasted into this column "DEST_LOC_ID" in my "PasteTable" id like it to automatically trim it.

I guess im struggling because I cant get the trim to work, seems to be failing with the rang

Private Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("D:D")) Is Nothing Then
     Else: GoTo Skip:
     End If
     Application.ScreenUpdating = False
     
     Dim SPS As Worksheet: Set SPS = Sheets("Paste Sheet")
     Dim SPT As ListObject: Set SPT = SPS.ListObjects("PasteTable")
     Dim PRng As Range: Set PRng = Range("SPT[DEST_LOC_ID]")
    
    For Each Cell In PRng
    Cell.Value = Trim(Cell.Value)
    Next Cell


    Application.ScreenUpdating = True
Skip:
End Sub

 

[Joe4]

How about before the sort step, you select the column and do a Find and Replace on the whole column, replacing a single space with nothing?
That should remove all spaces, and allow you custom sort to work the way you want.