Excel VBA Code Sort Left to Right - Please Help!

HSSVBA

New Member
Joined
Jan 1, 2019
Messages
4
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!

Code:
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi & welcome to MrExcel
How about
Code:
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
 
Upvote 0
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?
 
Upvote 0
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"?
 
Upvote 0
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...
 
Upvote 0
Hi & welcome to MrExcel
How about
Code:
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!
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0
Correct. Reverse the current order which is also sorting newest to oldest dates...
Here is the code I came up with for this scenario...
Code:
[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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top