VBA code to sort text BEFORE numbers?

wrecclesham

New Member
Joined
Jul 24, 2019
Messages
45
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".
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,638
Office Version
365
Platform
Windows
You could put this formula into a blank column & then sort on that
=IF(ISTEXT(A1),1,A1)
 

Richard U

Active Member
Joined
Feb 14, 2006
Messages
377
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
 

wrecclesham

New Member
Joined
Jul 24, 2019
Messages
45
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:

wrecclesham

New Member
Joined
Jul 24, 2019
Messages
45
Also, how can I add your code to my existing code so that it automatically runs whenever any values are changed in the worksheet?
 

wrecclesham

New Member
Joined
Jul 24, 2019
Messages
45
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,638
Office Version
365
Platform
Windows
Did you try my suggestion using a helper column?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
This just sorts the dates then sends the non dates to top of list !!
Code:
[COLOR="Navy"]Sub[/COLOR] MG01Aug13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).Resize(, 4)
Rng.Sort Range("D2")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Columns(4).Cells
    [COLOR="Navy"]If[/COLOR] Not IsDate(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn.Offset(, -3).Resize(, 4)
            Else: [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn.Offset(, -3).Resize(, 4))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
nRng.Cut
Range("A2").Insert shift:=xlDown
Regards Mick
 
Last edited:

wrecclesham

New Member
Joined
Jul 24, 2019
Messages
45
@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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,638
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,085,316
Messages
5,382,928
Members
401,809
Latest member
jbeanx80

Some videos you may like

This Week's Hot Topics

Top