sort ascending/descending vba excel not working

Question

I'm building a macro that sort ascending/descending a range of cells according to its values. The problem is that it is not working with the following data:

11_NR-10.pdf 16_NR-10.pdf 1_NR-10.pdf 6_NR-10.pdf

When I try to sort, I get the following result:

1_NR-10.pdf 11_NR-10.pdf 16_NR-10.pdf 6_NR-10.pdf

Does someone knows how to help me?

Code:

Dim xlSort As XlSortOrder
Dim LastRow As Long

With ActiveSheet

     LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

     If (.Range("A3").Value > .Range("A" & CStr(LastRow))) Then
         xlSort = xlAscending
     Else
         xlSort = xlDescending
     End If

     .Range("A3:A" & LastRow).Sort Key1:=.Range("A3"), Order1:=xlSort, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal


End With
ActiveWorkbook.Save

Well the sort is working correctly. The alphabetic sort is 1, 11, 2, 22 etc. not the realistic 1, 2, 11, 22. If you change the files names to 01, 11, 02, 22. The sort will be 01, 02, 11, 22. — PaulFrancis
– PaulFrancis, Commented Dec 9, 2014 at 13:18
If you place those four file names into four different column cells and ask Excel to sort them A-Z, that's exactly the sorting order you would get. Then, your code is correct -- in the sense that it is replicating Excel's spreadsheet behaviour. — Fausto Arinos Barbuto
– Fausto Arinos Barbuto, Commented Dec 9, 2014 at 13:22
@PaulFrancis unfortunately I can't change the file names. It cannot contain any 0 on the left side. — Alex.Six
– Alex.Six, Commented Dec 9, 2014 at 13:25
@FaustoArinosBarbuto Unfortunately the file names needs to be in the same column — Alex.Six
– Alex.Six, Commented Dec 9, 2014 at 13:33

Makah · Accepted Answer · 2014-12-09 14:41:28Z

1

I have an auxiliar function just for this. Full untested code below :

Public Sub MySuperSort()
    Dim sortType31 As Integer, lastRow As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    sortType = ([A3] > Cells(lastRow, 1))
    Call MyOrder(Range(Cells(3, 1), Cells(lastRow, 1)), 1, False)

    ActiveWorkbook.Save
End Sub

Private Sub MyOrder(ByVal tableRange As Range, ByVal columnIndex As Integer, ByVal ascending As Boolean, Optional ByVal header As Boolean = True)
    Dim orderBy As Integer, hasHeader As Integer
    orderBy = IIf(ascending, xlAscending, xlDescending)
    hasHeader = IIf(header, xlYes, xlNo)

    With tableRange.Parent
        .Sort.SortFields.Clear
        .Sort.SortFields.Add _
            Key:=Intersect(tableRange, tableRange.Columns(columnIndex)), _
            SortOn:=xlSortOnValues, Order:=orderBy, DataOption:=xlSortNormal
        With .Sort
            .SetRange tableRange
            .header = hasHeader
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

edited Dec 9, 2014 at 14:41

answered Dec 9, 2014 at 13:18

Makah

4,5233 gold badges52 silver badges68 bronze badges

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

7 Comments

Alex.Six Over a year ago

You mean like: MyOrder (.Range(.Cells(3,1).Cells(lastRow,1), 1, false) ?

Makah Over a year ago

Yes Call MyOrder(.Range(.Cells(3,1).Cells(lastRow,1), 1, false)

Alex.Six Over a year ago

Sorry, it may look silly but where do I call MyOrder? Can I create a button and then simply call it passing its arguments?

Alex.Six Over a year ago

It's returning syntax error on Call MyOrder(.Range(.Cells(3,1).Cells(lastRow,1), 1, false) . I'll try to fix it then I'll post it again if I still have problem

Makah Over a year ago

Forgot ")". Try it again.

|

Collectives™ on Stack Overflow

sort ascending/descending vba excel not working

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related