Sorting entire column's data based on dates in a row (VBA)

[Keojey]

Hello,

I've been trying to find a way to sort data that gets added to a sheet with VBA. What it needs to do is pretty straight forward, so I'll just give an example of what I'd like it to do :

I'm trying to get data that looks like so :

And have the dates sort from left to right, oldest to newest; but then have it move the three columns below each date in tandem with it. So, taking the above example and turn it into this :

So I have the general idea where I can sort the dates with a code like so :

Sub testsort()

    Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

But I'm not so sure what lies beyond this to get the desired results. Hopefully it's not too complicated

Just to state it; there is no more or less to this specific sheet other than the fact that the idea is to have data constantly build up in it over time. So the date will always be in row 1. 1st, 2nd, and 3rd will always be in row 2 in that order. The rest of the numbers are quantities that should stay in order with it's respective date and 1st, 2nd, or 3rd entry.

Any help would truly be appreciated!

 

[johnnyL]

Can you post your data in XL2BB format?

 

[kevin9999]

Are they merged cells in your top row?

 

[kevin9999]

My gut feeling is that you have merged cells in row 1, and that's what's causing you problems. If that is the case, then the following code unmerges the cells, sorts the data, then remerges them again. Try it on a copy of your data.

Option Explicit
Sub Sort_L_to_R()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, c As Range
    Dim lc As Long, i As Long
    Set ws = Worksheets("Sheet1")
    lc = Cells.Find("*", , xlValues, , 2, 2).Column
    
    For Each c In ws.Cells(1, 1).Resize(1, lc)
        With c.MergeArea
            .UnMerge
            .Formula = c.Formula
        End With
    Next c
    
    ws.Range(Columns(1), Columns(lc)).Sort Key1:=ws.Range("A1"), Order1:=1, Orientation:=2
    
    Application.DisplayAlerts = False
    For i = 1 To lc - 2
        ws.Range(Cells(1, i), Cells(1, i + 2)).Merge
        i = i + 2
    Next i
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
End Sub

 

[Keojey]

Can you post your data in XL2BB format?

Sorry! I was doing all this on my work computer and it won't let me download the add-on to excel over here to be able to import it to that format. It was ignorant of me to think a picture was enough information.

My gut feeling is that you have merged cells in row 1, and that's what's causing you problems. If that is the case, then the following code unmerges the cells, sorts the data, then remerges them again. Try it on a copy of your data.

Option Explicit
Sub Sort_L_to_R()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, c As Range
    Dim lc As Long, i As Long
    Set ws = Worksheets("Sheet1")
    lc = Cells.Find("*", , xlValues, , 2, 2).Column
   
    For Each c In ws.Cells(1, 1).Resize(1, lc)
        With c.MergeArea
            .UnMerge
            .Formula = c.Formula
        End With
    Next c
   
    ws.Range(Columns(1), Columns(lc)).Sort Key1:=ws.Range("A1"), Order1:=1, Orientation:=2
   
    Application.DisplayAlerts = False
    For i = 1 To lc - 2
        ws.Range(Cells(1, i), Cells(1, i + 2)).Merge
        i = i + 2
    Next i
    Application.DisplayAlerts = True
   
    Application.ScreenUpdating = True
End Sub

Your gut feeling was correct! My apologies for the slight confusion.
Otherwise, this works perfectly for what I need it to do! It seems to timeout and give me a "400" error when I try to call it from another Sub for some odd reason, but I can still make use of this regardless if it's called from another sub or not.

Thank you!

 

[kevin9999]

Glad we could help, and thanks for the feedback.