Sorting a column numerically while ignoring the preceding letter

[VBAnoob21]

Greetings, thank you for clicking on my thread! I am in need of your help.

I am working on sorting a spreadsheet based on criteria in column C. Column C has a list of numbers, all of which are preceded by the letter A or B.

Example:
A72200100
B7810210
B72300100
A7810220

I want to write some VBA code to sort these numerically in ascending order while ignoring the preceding letter (either A or B). So I would like to see the following in column C after the code has been run:

B7810210
A7810220
A72200100
B72300100

I also need to make sure the entire row follows its original entry in column C.

Thank you very much for your time!

-D

 

[wideboydixon]

You *could* add an extra column to the data:

=NUMBERVALUE(MID(C1,2,LEN(C1)))

You could then sort on that column. If you don't want to do this then you'll need to use a macro.

WBD

 

[VBAnoob21]

Hi wideboydixon,

Yes I am trying to avoid that at all costs. I do not want to add another column. If anyone could help me out with this macro it would be greatly appreciated!

 

[Eric W]

Here's a macro that should do what you want. It essentially does what WBD suggested, but automatically. Adds a column, inserts the formula, sorts the columns, then deletes the added column. It's far easier and more efficient to use Excel's built-in sort function that it would be to write one.

1) Open your workbook (preferably a copy)
2) Right click on the sheet tab on the bottom and select View Code
3) From the menu, select Insert > Module
4) On the sheet that opens, paste this code

Sub SortNums()
Dim MyCol As Range, MaxRow As Long
    
    Set MyCol = Columns("C:E")
    
    Columns(MyCol.Column + 1).Insert Shift:=xlToRight
    MaxRow = MyCol.Cells(Cells.Rows.Count, 1).End(xlUp).Row
    Range(MyCol.Cells(1, 2), MyCol.Cells(MaxRow, 2)).FormulaR1C1 = "=MID(RC[-1],2,999)"
    
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=MyCol.Cells(1, 2), DataOption:=xlSortTextAsNumbers
        .SetRange Range(Cells(1, MyCol.Column), Cells(MaxRow, MyCol.Column + MyCol.Columns.Count - 1))
        .Apply
    End With
    Columns(MyCol.Column + 1).Delete Shift:=xlToLeft
    
End Sub

5) Change the column references (in red) to the columns you want to sort.
6) Press Alt-Q to close the VBA editor
7) Press Alt-F8 to open the macro selector
8) Select SortNums and press Run.

Let me know if this works for you.

 

[VBAnoob21]

Eric,

Thanks for your reply. I ran the code in the debug window line by line and it looks like there isn't a whole lot of sorting going on. I see the new column D pop up after line 3 of your code. Line 4 then copies exactly what is in column B into my column C, then the new one is deleted, so columns B and C are identical. It does look like some of the colors have shifted around.

Sorry I cannot explain this better but I am all new and completely clueless to VBA. I cannot send the spreadsheet either because it is proprietary information for my job. Hopefully you are still able to help me.

Thanks.

 

[Eric W]

From your description, it sounds like you set the range to something like "A:G". I assumed that the leftmost column in the range would be C, which is the key column. I modified the macro slightly so that there's one range for the columns you want to sort, and another range for the key, which I assume is C. Change the ranges in red as needed:

Sub SortNums()
Dim MyCol As Range, SortCol As Range, MaxRow As Long
    
    Set MyCol = Columns("A:E")
    Set SortCol = Columns("C:C")
    
    Columns(SortCol.Column + 1).Insert Shift:=xlToRight
    MaxRow = SortCol.Cells(Cells.Rows.Count, 1).End(xlUp).Row
    Range(SortCol.Cells(1, 2), SortCol.Cells(MaxRow, 2)).FormulaR1C1 = "=MID(RC[-1],2,999)"
    
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=SortCol.Cells(1, 2), DataOption:=xlSortTextAsNumbers
        .SetRange Range(Cells(1, MyCol.Column), Cells(MaxRow, MyCol.Column + MyCol.Columns.Count - 1))
        .Apply
    End With
    Columns(SortCol.Column + 1).Delete Shift:=xlToLeft
    
End Sub

 

[VBAnoob21]

Eric,
You were right about the range.

We are definitely getting somewhere now. Portions of column C are arranged correctly but I have lots of merged cells in my spreadsheet which I feel is causing issues. Lots of color coding mismatches and some cells with nothing in them. Any way around this?

 

[Eric W]

Without knowing the location of the merged cells, it's difficult to account for them. This version of the macro inserts the work column before column A, which ensures that it won't affect any merged cells. However, it does mean that the sort range (MyCol) MUST start in column A.

Sub SortNums()
Dim MyCol As Range, SortCol As Range, MaxRow As Long
    
    Set MyCol = Columns("A:E")
    Set SortCol = Columns("C:C")
    
    Columns(1).Insert Shift:=xlToRight
    MaxRow = SortCol.Cells(Cells.Rows.Count, 1).End(xlUp).Row
    Range(Cells(1, 1), Cells(MaxRow, 1)).FormulaR1C1 = "=MID(RC[" & SortCol.Column - 1 & "],2,999)"
    
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Cells(1, 1), DataOption:=xlSortTextAsNumbers
        .SetRange Range(Cells(1, 1), Cells(MaxRow, MyCol.Column + MyCol.Columns.Count - 1))
        .Apply
    End With
    Columns(1).Delete Shift:=xlToLeft
    
End Sub

 

[VBAnoob21]

I cleaned up my spreadsheet and got rid of all of my merged cells because they weren't really necessary.

Your macro works great and did exactly what I needed. Thank you kindly, sir.

 

[Eric W]

You're welcome.