Excel VBA Sort Based on A String

Question

I would like to sort a sheet based on a string for example:

Column 1 Column 2
name1     x
name2     x
name3     asdad
name4     gadfa
name5     gsdff

Is it possible to sort this in a way that it places all rows with a certain string on top, in this case, all rows with the "x" string should be on top first and then it sorts the rest alphabetically based on column 2.

You can also actually record a macro with this one and edit or remove unnecessary lines :) — Eem Jee
– Eem Jee, Commented Jul 29, 2018 at 9:36

YasserKhalil · Accepted Answer · 2018-08-01 05:01:57Z

2

Try this code

Sub Test()
Dim n           As Long

Application.AddCustomList Array("x")
n = Application.CustomListCount

With Sheets("Sheet1")
    .Range("A1").CurrentRegion.Sort Key1:=.Range("B1"), Header:=xlYes, OrderCustom:=n + 1
    .Sort.SortFields.Clear
End With

Application.DeleteCustomList n
End Sub

edited Aug 1, 2018 at 5:01

answered Jul 29, 2018 at 10:24

YasserKhalil

9,60813 gold badges62 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vince Gonzales Over a year ago

When I save the excel file after calling the AddCustomList, it crashes though. What would be the cause of this?

YasserKhalil Over a year ago

I have added a line to clear the sort in the activesheet. See if this works for you

Collectives™ on Stack Overflow

Excel VBA Sort Based on A String

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related