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!

 

[Joe4]

Does your data always start up in cell A1?

 

[zalik22]

Yes it does! But the number of columns and rows might change.

 

[Joe4]

Try this:

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)
    
'   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]

Thanks it worked!

 

[Joe4]

You are welcome!

 

[zalik22]

Hi, I spoke too soon. Most of the column is sorted, but not all. Any tips?

 

[zalik22]

I think it has something to do with the date format. I need to add code for "sort values that look like a date as date"

 

[Joe4]

Hi, I spoke too soon. Most of the column is sorted, but not all. Any tips?

It works on the Contiguous Range.
That means that it will go as far out as the first totally blank column, and down as far as the first totally blank row.
If you have some columns that are not being sorted, be sure to check to see if you have any blank columns between them and the rest of the data.
If you do not have this going on, do these columns that aren't sorting write contain formulas?

 

[zalik22]

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