Sort Dates Stored As Text

  • I record a macro (see attached workbook) to sort a range and I get asked a question about whether I'd like to sort anything that looks like anumber, as a number, or to sort numbers and text separately. I choose the former and get this macro:

    Sub Macro3()
        Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    End Sub

    I want the result that I get on the sheet as I'm recording the macro (as in the attached). When I run the macro, it seems to ignore the
    DataOption1:=xlSortTextAsNumbers bit while resulting in what seems to be a normal sort, which is not what I want.
    This applies to Excel 2002 and 2003, under XP.
    Am I missing something?

    regards, Pascal

  • Re: Sort Dates Stored As Text

    while that is a solution, the question was more related to there being different results from the sorting process when doing it manually, from when running a macro recorded doing it manually.
    There is functionality within Excel which seems to work - sometimes. Or is it me?
    You've changed the thread title too, which loses that thrust of the question.
    regards, Pascal

  • Re: Sort Dates Stored As Text

    I'm attempting to get a macro to do the same thing as I do manually. Recording then running the macro does a different thing, from what I recorded. It looks like one of the arguments is being ignored, namely DataOption1:=xlSortTextAsNumbers, or I've cocked up.

    If I go through the dropdown menus and sort a range, it gives me what I want. If I record a macro doing the same thing, when I've finished recording I've got what I want. When I run the macro I've just recorded, I DON'T get what I want.

    I prefer not to start changing strings to true dates every time, unless it can be very quick. Some dates are strings, some real dates, that's what I've got to work with. Anyway, why should it be necessary if the sort works properly when done using the dropdowns, I'd expect the macro to be able to do it in the same way? Or I've cocked up.

    I just need someone to point out the mistake I've made, or that it's perhaps a bug and live with it.

    What's missing from the thread title is that I'm sorting a mixture of dates and strings looking like dates. A thing I can do straightforwardly via the dropdown menus, but not via a macro -yet.
    regards, Pascal

  • Re: Sort Dates Stored As Text


    I'm attempting to get a macro to do the same thing as I do manually

    Which is Sort Dates Stored As Text

    I don't condone storing dates or numbers as text, hence my solution as apposed to a band-aid workaround.

    With your Assumed Experience level, you should know how to quickly convert dates as text to true dates :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!