Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: VBA code to sort text BEFORE numbers?

  1. #11
    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?

    @MickG

    Quote Originally Posted by MickG View Post
    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
    I'm having some trouble getting your VBA to work for me but it looks promising.

    Does it just need an "End Sub" after it?

    What about before? Sorry for the basic questions, I'm new to this!
    Last edited by wrecclesham; Aug 1st, 2019 at 11:26 AM.

  2. #12
    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?

    Yes, just End Sub at the bottom, sorry about that , copying Error !!

  3. #13
    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?

    I tried using this:

    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
    End Sub
    But I get this error:

    VBA code to sort text BEFORE numbers?

    Any ideas?

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

    Default Re: VBA code to sort text BEFORE numbers?

    How about
    Code:
    Sub wrecclesham()
       With Range("A1", Range("A" & Rows.Count).End(xlUp))
          .Offset(, 4).Formula = "=IF(ISTEXT(D1),1,D1)"
          .Resize(, 5).Sort key1:=Range("e1"), order1:=xlAscending, Header:=xlYes
          .Offset(, 4).Clear
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    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?

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub wrecclesham()
       With Range("A1", Range("A" & Rows.Count).End(xlUp))
          .Offset(, 4).Formula = "=IF(ISTEXT(D1),1,D1)"
          .Resize(, 5).Sort key1:=Range("e1"), order1:=xlAscending, Header:=xlYes
          .Offset(, 4).Clear
       End With
    End Sub
    Just tried using that code. Getting a run-time error 1004. Hmm.

    Feels like this is nearly solved! Just need to figure out why I'm getting a run-time error on my end and to restrict the sorting to A2:D11.
    Last edited by wrecclesham; Aug 1st, 2019 at 11:57 AM.

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

    Default Re: VBA code to sort text BEFORE numbers?

    If you only want A1:D11 then use
    Code:
    Sub wrecclesham()
       With Range("A1:D11")
          .Offset(, 4).Formula = "=IF(ISTEXT(D1),1,D1)"
          .Resize(, 5).Sort key1:=Range("e1"), order1:=xlAscending, Header:=xlYes
          .Offset(, 4).Clear
       End With
    End Sub
    Which line gives the error & what is the error message?
    Also is the sheet protected & do you have any merged cells?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    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?

    That fixed it!

    There was a merged cell in another row, outside of A2:D11. Targeting only these rows prevents any errors appearing at run time.

    The dates and text strings are being sorted properly now.

    The only issue I'm having at this point is that blank rows (i.e. rows with blank cells in the D column) are being sorted above all rows with data.

    Any idea how I can get this script to also move any blank rows to the bottom, instead of stacking them at the top?
    Last edited by wrecclesham; Aug 1st, 2019 at 03:16 PM.

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

    Default Re: VBA code to sort text BEFORE numbers?

    How about
    Code:
    Sub wrecclesham()
       With Range("A1:A11")
          .Offset(, 4).Formula = "=IF(ISTEXT(D1),1,if(d1="""",9999999,D1))"
          .Resize(, 5).Sort key1:=Range("e1"), order1:=xlAscending, Header:=xlYes
          .Offset(, 4).Clear
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    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?

    Works perfectly! Thank you.

    I'm trying to insert your code into my existing macro so that it continues to automatically sort the data whenever there are any changes, just using the improved logic.

    Here's a simplified version of what I have right now (with most of the old code removed). The first and last lines and the sort logic that is being improved are shown here:

    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
    How can I modify the above code so that your new code replaces it and is run whenever the worksheet is updated?

    I'm not really sure about the syntax.
    Last edited by wrecclesham; Aug 1st, 2019 at 04:31 PM.

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

    Default Re: VBA code to sort text BEFORE numbers?

    Simply delete everything except the sub & end sub lines & replace it with the code I supplied (less the sub & end sub lines)
    - 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
  •