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

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121
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?

Code:
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
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Upvote 0
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
Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top