CODEWITHSUNDEEP
excelvba
user1624926
user1624926

Reputation: 451

Sort Numbers/Text in one column

I have wrote the following code to sort data on my workbook

With gwksSheet
    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=Columns(glAssetTypeCol), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Columns(glFundCodeCol), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Columns(glOberonCol), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range(Cells(1, 1), Cells(glLastRow, glLastCol))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

In the column "glOberonCol" I have numbers (e.g. 2561236) & Text (e.g. 2561236R). When it sorts it sorts the numbers and then the text. I want to sort it so I get 2561236 & 2561236R beside each other. What do I need to do. Appreciate any help.

Thanks,

Ciaran.

Upvotes: 1

Views: 87

Answers (1)

Makah
Makah

Reputation: 4513

Follow the steps bellow:

  • Create another column
  • Add formula "=TEXT(RC[-1],"###")"
  • Copy this value and override the old values forcing Excel to see numbers as strings.
  • Now you can use your macro or just use Filter button.

Upvotes: 2

Related Questions