VBA code to sort text BEFORE numbers?

[wrecclesham]

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

    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]

You could put this formula into a blank column & then sort on that
=IF(ISTEXT(A1),1,A1)

 

[Richard U]

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]

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?

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

 

[wrecclesham]

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]

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!

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

 

[Fluff]

Did you try my suggestion using a helper column?

 

[MickG]

Try this:-
This just sorts the dates then sends the non dates to top of list !!

[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

 

[wrecclesham]

@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?

 

[Fluff]

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.