Excel VAB Refuses to Sort Ascending

[Shiseiji]

If I sort the column manually, accept text as number, no problem. But the code below refuses to sort the column. What am I missing???
I changed the sort range from a named range to bypass the header in my efforts to get this to run . . .

TIA!!!

Ron
NB: Had troubles with filters, so left in both possibilities.

Sub m_AD_Sort_ADATE()
'
Application.Volatile True
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    '
    Sheets("AD").Select
    ActiveWindow.DisplayGridlines = True
    ActiveWindow.FreezePanes = False
    '
    Dim LastCol                 As Integer
    Dim LastRow                 As Long
    Dim Thiswb                  As Workbook
    Dim Thiswksht               As Worksheet
    Set thisws = ActiveSheet
    Set Thiswb = ActiveWorkbook
    '
    With Thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    '
    If ActiveSheet.FilterMode Then
       ActiveSheet.ShowAllData
    End If
    '
    If ActiveSheet.FilterMode = True Then
      ActiveSheet.FilterMode = False
    End If
    '
    ActiveWindow.FreezePanes = False
    '
    thisws.Sort.SortFields.Clear
    '
    Range("c_AD_ADATE").Select
    '
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .NumberFormat = "dd/mm/yyyy"
    End With
    '
    Cells(2, ActiveCell.Column).Select
    Range(ActiveCell.Address, Cells(LastRow, ActiveCell.Column)).Select
    Selection.Sort Key1:=Range("c_AD_ADATE"), Order1:=xlAscending, DataOption1:=xlSortTextAsNumbers
    '
    With ActiveWorkbook.Worksheets("AD").Sort
        .SetRange Range("tbl_AD")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    '
    ActiveWorkbook.Worksheets("AD").Sort.SortFields.Clear
    Cells(1, ActiveCell.Column).Select
    AutoFilter = False
    '
    'Cells(1, 1).Select
    'ActiveCell.Offset(1, 0).Select
    'Cells(1, 1).Select
    '
End Sub

 

[Jeffrey Mahoney]

I think you need to change this line:

Selection.Sort Key1:=Range("c_AD_ADATE"), Order1:=xlAscending, DataOption1:=xlSortTextAsNumbers

TO

Activesheet.Sort.SortFields.Add Key:=Range("c_AD_ADATE"), Order:=xlAscending, DataOption:=xlSortTextAsNumbers

 

[Shiseiji]

Thanks Jeffrey, sadly no joy. As soon as I can leave Virginia, Washington is where we're headed!

R

 

[Shiseiji]

Had an idea. I resorted and told Excel to sort the text and dates separately. 543 (what a number LOL) of my "dates" were text. Some of the date entries are a result of not finding a date and rather then have null cells, I enter a date outside of my range so they are always not counted. That's probably why I was seeing them on both ends of my data set. Still not sure why Excel wouldn't treat the text as a date . . .

The original data has to be converted using Text-to-Columns and who knows why these 543 cells are different. Something else to figure out. At least it's a clue. In the interest of time-management, as Excel refuses to sort the text as numbers, I think I'll try an IFERROR with DATEVALUE to see if I can clean these up so they sort properly.

Of course I'm always open to suggestions! I'm a code hack and freely admit it.
Thanks again

Ron

R