Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: VBA code to sort text BEFORE numbers?

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Wrecclesham, Farnham, UK
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post VBA code to sort text BEFORE numbers?

    I use the following VBA code to sort a list of dates in ascending order.

    Code:
        Range("A1:B10").Sort Key1:=Range("A1"), _
          Order1:=xlAscending, Header:=xlNo, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    The problem is that in some cells in the date column, the value is a word rather than a date, and those rows must be sorted above all of the dates. Right now, the cells with text values are moved to the bottom of the list.

    Does anyone know how I can modify my existing code to change the sort order slightly, so that any text strings appear at the top of my list, instead of at the bottom?

    The dates must still be in ascending order, so I can't solve this by simply switching the sort order to "descending".

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA code to sort text BEFORE numbers?

    You could put this formula into a blank column & then sort on that
    =IF(ISTEXT(A1),1,A1)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Feb 2006
    Location
    NJ, USA
    Posts
    354
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to sort text BEFORE numbers?

    Code:
    Option Explicit
    
    
    
    Sub sorter()
    Dim ctr As Long
    
        Range("A1:B10").Sort Key1:=Range("A1"), _
          Order1:=xlAscending, Header:=xlNo, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
        ctr = 1
        Do Until Not IsNumeric(Cells(ctr, 1).Value)
            ctr = ctr + 1
        Loop
        Range("A" & ctr & ":B10").Select
        Selection.Cut
        Range("A1:B1").Select
        Selection.Insert Shift:=xlDown
    
    
    End Sub
    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    Also, when asking questions, please be as detailed as possible, and If I have helped you, please let me know.
    I don't require thanks, but acknowledgement is nice

  4. #4
    New Member
    Join Date
    Jul 2019
    Location
    Wrecclesham, Farnham, UK
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to sort text BEFORE numbers?

    Hi Richard,

    I'm trying to adapt your code to sort by the values in D2:D11.

    Range of rows to sort: A2:D11.

    Does this look okay?

    Code:
    Option Explicit
    
    Sub sorter()
    Dim ctr As Long
    
        Range("A2:D11").Sort Key1:=Range("D2:D11"), _
          Order1:=xlAscending, Header:=xlNo, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
        ctr = 1
        Do Until Not IsNumeric(Cells(ctr, 1).Value)
            ctr = ctr + 1
        Loop
        Range("A" & ctr & ":D11").Select
        Selection.Cut
        Range("A2:D11").Select
        Selection.Insert Shift:=xlDown
    
    
    End Sub
    Last edited by wrecclesham; Jul 31st, 2019 at 11:31 AM.

  5. #5
    New Member
    Join Date
    Jul 2019
    Location
    Wrecclesham, Farnham, UK
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to sort text BEFORE numbers?

    Also, how can I add your code to my existing code so that it automatically runs whenever any values are changed in the worksheet?

  6. #6
    New Member
    Join Date
    Jul 2019
    Location
    Wrecclesham, Farnham, UK
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to sort text BEFORE numbers?

    Here is a screenshot showing exactly what I'm trying to accomplish, including the real cell ranges in question.



    1) Rows A2:D11 should be arranged in ascending order on the values in D2:D11
    2) Cells containing text in D2:D11 should be sorted above cells containing numbers (not below them, which is the default behavior for ascending order sorting)

    I currently use the following code to perform alphabetical sorting of these rows on the "Due date" column. The only problem is that it puts numbers below letters.

    If someone could figure out what I can replace this existing code with in order to get something that continues to run automatically whenever any cell values change, I would appreciate it!

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Range("A2:D11").Sort Key1:=Range("D1"), _
          Order1:=xlAscending, Header:=xlNo, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
        
    End Sub
    Last edited by wrecclesham; Aug 1st, 2019 at 08:55 AM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA code to sort text BEFORE numbers?

    Did you try my suggestion using a helper column?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: VBA code to sort text BEFORE numbers?

    Try this:-
    This just sorts the dates then sends the non dates to top of list !!
    Code:
    Sub MG01Aug13
    Dim Rng As Range, Dn As Range, nRng As Range
    Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).Resize(, 4)
    Rng.Sort Range("D2")
    For Each Dn In Rng.Columns(4).Cells
        If Not IsDate(Dn.Value) Then
            If nRng Is Nothing Then
                Set nRng = Dn.Offset(, -3).Resize(, 4)
                Else: Set nRng = Union(nRng, Dn.Offset(, -3).Resize(, 4))
            End If
        End If
    Next Dn
    nRng.Cut
    Range("A2").Insert shift:=xlDown
    Regards Mick
    Last edited by MickG; Aug 1st, 2019 at 09:49 AM.

  9. #9
    New Member
    Join Date
    Jul 2019
    Location
    Wrecclesham, Farnham, UK
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to sort text BEFORE numbers?

    @Fluff

    The reason I favour the VBA approach is that I would like it to dynamically sort whenever any values are updated in the worksheet.

    If I use a helper column and (manually?) sort on that, I will have to manually sort again whenever the data is changed.

    Or is the idea with using a helper column just to use it for the "letters before numbers" aspect of the sorting logic, and it would be used in conjunction with a VBA script to ensure the sorting happened dynamically?
    Last edited by wrecclesham; Aug 1st, 2019 at 09:53 AM.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA code to sort text BEFORE numbers?

    It's just to get the correct sort order, you can use the code to add the formula, sort & then delete the helper column if needed.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •