VBA code to sort text BEFORE numbers?

wrecclesham

Board Regular
Joined
Jul 24, 2019
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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".
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
You could put this formula into a blank column & then sort on that
=IF(ISTEXT(A1),1,A1)
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Also, how can I add your code to my existing code so that it automatically runs whenever any values are changed in the worksheet?
 
Upvote 0
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:
Upvote 0
Did you try my suggestion using a helper column?
 
Upvote 0
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:
Upvote 0
@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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top