Sort Data in Ascending Order Based on Column Header Name

[zalik22]

Hi,

Need help with some VB code please. I would like to sort data in ascending order based on column headers, the number of columns and rows may differ. So, sort ascending based on column header "Date".

Thanks in advance!

 

[Greg Truby]

No formulas, but the sort is sorting 8/1, then 8/10, then 8/11, then 8/2.

Then your dates ain't being stored as dates. They're being stored as text. Try using the Text to Columns tool on the Data tab to convert them to dates and then sort.

 

[Joe4]

No formulas, but the sort is sorting 8/1, then 8/10, then 8/11, then 8/2.

Aha!
The issue is that you are not, in fact, working with dates, but rather with Text entries. In text, where you sort by characters from left to right, 8/11 comes before 8/2.

If you convert your entries to dates, they will sort properly.
You could do that all at once by highlighting the column, doing a Text to Columns, and selecting "Date" for the column Format in Step 3. That should convert them to valid dates (and you can format the column any way you like), and it should sort properly.


Edit: Yeah, what Greg said, only faster!

 

[zalik22]

Can I put code to do this or do I have to manually do it?

 

[zalik22]

This is a sample of one of the fields, I need to retain the time. 7/27/2017 7:37 AM

 

[Joe4]

Sure

Sub DynamicSort()

    Dim sortAdd As String
    Dim sortRange As Range

'   Find which column "Date" appears in
    On Error GoTo err_chk
    Rows("1:1").Find(What:="Date", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
    sortAdd = ActiveCell.Address(0, 0)
   
'   Convert entries in Date column to valid dates
    Columns(ActiveCell.Column).TextToColumns Destination:=Range(sortAdd), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
    
'   Format columns
    Columns(ActiveCell.Column).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
    
'   Set sort range by using current region
    Range("A1").CurrentRegion.Sort _
        key1:=Range(sortAdd), order1:=xlAscending, Header:=xlYes
        
    Exit Sub
    
'   Error handling
err_chk:
    If Err.Number = 91 Then
        MsgBox "No header row with title of Date", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[zalik22]

Looks good, is there a way to keep the AM/PM in the dates? I noticed it changed 4pm to 16:00.

 

[Greg Truby]

Looks good, is there a way to keep the AM/PM in the dates? I noticed it changed 4pm to 16:00.

It shouldn't have. Did you include Joe's line here?

'   Format columns
    Columns(ActiveCell.Column).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

And, just for grins, a feller could also use PasteSpecial | Add to solve this problem. In VBA that'd look something like so (this was just as a demo, code ain't tweaked to yer specifics).

Sub CoerceUsingPasteSpecial()

    Const c_strTimeFormat As String = "mm/dd/yyyy h:mm am/pm"


    Dim celTemp     As Excel.Range, _
        rngTarget   As Excel.Range
        
    
    '// we're gonna target first column in the worksheet in
    '// our example here
    With ActiveSheet
        Set rngTarget = .UsedRange.Columns(1)
        Set celTemp = .Cells(1, .UsedRange.Columns.Count + 1)
    End With
        
    '// assuming single-row header
    Set rngTarget = rngTarget.Offset(1).Resize(rngTarget.Rows.Count - 1)
    With celTemp
        .NumberFormat = c_strTimeFormat
        .HorizontalAlignment = XlHAlign.xlHAlignLeft
    End With
    
    celTemp.Copy
    rngTarget.PasteSpecial Operation:=xlPasteSpecialOperationAdd
    celTemp.Clear


End Sub

 

[Joe4]

It doesn't do that for me. It shows 4:00 PM.
You might have different regional settings.

Here is how you can get the code that you need:
1. Turn on the Macro Recorder
2. Apply the date format that you want to a cell (make sure it is showing the way that you want)
3. Stop the Macro Recorder and view your code
4. Replace this part in my code:

.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

with what it is showing in the code that you recorded

 

[Greg Truby]

Also, were I you, I'd probably go ahead and use a number format along the lines of:

    Const c_strTimeFormat As String = "mm/dd/yyyy h:mm am/pm;;;""««« Illegal Date Value »»»"""

The reason being that since you're coercing this, you may have coersions that fail. If they fail, then the cell will almost certainly retain it's old value which Excel will almost certainly consider text. So I would set the fourth argument of the number format (which formats text values) to something that would make failed coersions jump off the page at me.

 

[zalik22]

I modified the code a little but for some reason deleted the date/time formatting. I added the line of code and it worked, sorry for the miscommunication. Thanks again for the help!