Excel VBA Code Sort Left to Right - Please Help!

[HSSVBA]

Hi There -

I am trying to add code to an existing Excel VBA Macro that sorts the attached file left to right. However I need the number of columns and rows to be dynamic. Basically I need you to start in cell F1 and ctrl+shift RIGHT and ctrl+Shift DOWN to select the datarange and then sort based on the selection.

I need to sort Left to Right by Largest to Smallest.

I tried recording it but this is what I got and it isn't going to work for different sheets with different numbers of columns and rows.

I just joined today to ask this so I can't post attachments my sample excel file is located here: https://www.dropbox.com/s/kgal60jx9bpae6l/SortLefttoRight.xlsx?dl=0

Thanks in advance!

Sub sortlefttoright()
'
' sortlefttoright Macro
'

'
    Range("F1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Add Key:=Range("F1:J1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet1").Sort
        .SetRange Range("F1:J40")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub HSSVBA()
   Dim Hdr As Range, Rng As Range
   Set Hdr = Range("F1", Range("F1").End(xlToRight))
   Set Rng = Range(Hdr, Hdr.End(xlDown))
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add key:=Hdr, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Rng
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlLeftToRight
      .SortMethod = xlPinYin
      .Apply
   End With
End Sub

 

[Rick Rothstein]

I need to sort Left to Right by Largest to Smallest.

Is your original date in cells F1 to J1 (for your example file) always in ascending date order as shown in your example? I am asking because if they are, your question is not really one of sorting the columns, but rather reversing their current order, correct?

 

[HSSVBA]

Thank you so much for your quick response!

The code you provided did no change to my sheet. I want the newest dates to the left and the oldest to the right. Do I need "Order=xlDescending"?

 

[HSSVBA]

Is your original date in cells F1 to J1 (for your example file) always in ascending date order as shown in your example? I am asking because if they are, your question is not really one of sorting the columns, but rather reversing their current order, correct?

Correct. Reverse the current order which is also sorting newest to oldest dates...

 

[HSSVBA]

Hi & welcome to MrExcel
How about

Sub HSSVBA()
   Dim Hdr As Range, Rng As Range
   Set Hdr = Range("F1", Range("F1").End(xlToRight))
   Set Rng = Range(Hdr, Hdr.End(xlDown))
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add key:=Hdr, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Rng
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlLeftToRight
      .SortMethod = xlPinYin
      .Apply
   End With
End Sub

I changed from Ascending to Descending and it worked Beautifully!! THANKS!

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[Rick Rothstein]

Correct. Reverse the current order which is also sorting newest to oldest dates...

Here is the code I came up with for this scenario...

[table="width: 500"]
[tr]
	[td]Sub RearrangeDateColumnsLatestToEarliest()
  Dim LastRow As Long, LastCol As Long, NewOrder As Variant
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  NewOrder = Evaluate("TRANSPOSE(" & LastCol & "+6-ROW(6:" & LastCol & "))")
  Range("F1").Resize(LastRow, LastCol - 5) = Application.Index(Cells, Evaluate("ROW(1:" & LastRow & ")"), NewOrder)
End Sub[/td]
[/tr]
[/table]

Note 1: Just as an aside, if you run the code above a second time, it will put everything back in their original order.

Note 2: I do not know your work flow, but with my code, you cannot run the macro and then add more columns to the data and run the macro again! My code does not perform a sort, it simply quickly reverses the date columns as they stand.