Module Question

[MFish]

Hi there,

I'm trying to sort my table off of this code and it works great with letters to sort alphabetically, but numerically... It sucks.

Sub SortTable()


Range("B40").Select

    Set BillsT = Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, 5))
BillsT.Select
Worksheets("Master Data Sheet").Range(Selection, Selection).Sort _
        key1:=Worksheets("Master Data Sheet").Range(ActiveCell, ActiveCell)
    

End Sub

It will easily sort out, A to AA to BA to CF, etc. No problem. But, with numbers it doesn't. I have values like 1-1, 1-2, 2-1, 11-1 and it will sort 1-1, 1-2, 11-1, 2-1. I've tried to add a space between the numbers and "-" to see if the sorting will go numerically by 1, 2, 3, etc. but it still sorts it out like A, AA, B, BB format, if that makes sense. How do I get it to sort in order how I want it to?

 

[DanteAmor]

One option could be to separate the numbers in 2 new columns by the "-" and sort the information but with the 2 new columns:

Sub SortTable()


    Dim BillsT As Range, datos As Range
    
    Range("B40").Select
    Application.DisplayAlerts = False
    Set BillsT = Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, 5))
    BillsT.Columns(1).TextToColumns Destination:=BillsT.Cells(1, 1).Offset(0, 6), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
        OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True


    Set datos = BillsT.Resize(BillsT.Rows.Count, BillsT.Columns.Count + 2)
    datos.Sort key1:=BillsT.Cells(1, 1).Offset(0, 6), order1:=xlAscending, _
               key2:=BillsT.Cells(1, 1).Offset(0, 7), order2:=xlAscending, Header:=xlNo


    datos.Columns(7).Resize(datos.Rows.Count, 2).ClearContents
End Sub