Sort data with dynamic columns

Question

I have some values that are pasted over from another macro, always starting at C6 and spanning from row 6-11, however the number of columns will change every time.

What I'd like to do is to sort the data table by row 11 values, left to right, from small to large. So the sort range will always be "C6:?11"

Here is what I have right now, mainly from a recorded macro with a few edits:

Dim active As Worksheet
Set active = ActiveSheet

Range("C6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
active.Sort.SortFields.Clear
active.Sort.SortFields.Add2 Key:=Range( _
    "C11:I11"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
With active.Sort
    .SetRange Range("C6:I11")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlLeftToRight
    .SortMethod = xlPinYin
    .Apply
End With

I believe the problem lies with the "C11:I11" part, but I'm not sure what to change it to. The code also looks quite messy so if there's a better way to write this that'll be great.

I'm very new to VBA, any help would be appreciated!

`Key:=Range(Cells(3, 11), Cells(LastCol, 11))` where LastCol can be calculated. https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba — Tragamor, Aug 23 '21 at 23:20
Also: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Tragamor, Aug 23 '21 at 23:22

VBasic2008 · Accepted Answer · 2021-08-25T06:38:48.733

Sort Columns

First Column HardCoded

Option Explicit

Sub SortMyRange()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    Dim crg As Range ' Columns Range
    Set crg = RefColumns(ws.Range("B6:B11"))
    Dim srg As Range ' Sort Range
    Set srg = crg.Resize(, crg.Columns.Count - 1).Offset(, 1)
    Dim krg As Range
    Set krg = srg.Rows(srg.Rows.Count) ' Key Range
    Debug.Print crg.Address, srg.Address, krg.Address
    With ws.Sort
        With .SortFields
            .Clear
            .Add2 Key:=krg, SortOn:=xlSortOnValues, _
                Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange srg
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Creates a reference to the range from a column range
'               ('FirstColumnRange') to the column range containing
'               the right-most non-empty cell in the given column's rows.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefColumns( _
    ByVal FirstColumnRange As Range) _
As Range
    With FirstColumnRange.Columns(1)
        Dim lCell As Range
        Set lCell = .Resize(, .Worksheet.Columns.Count - .Column + 1) _
            .Find("*", , xlFormulas, , xlByColumns, xlPrevious)
        If lCell Is Nothing Then Exit Function ' empty range
        Set RefColumns = .Resize(, lCell.Column - .Column + 1)
    End With

End Function

First Cell HardCoded (Initial CurrentRegion Version)

Option Explicit

Sub SortMyRange()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    Dim crrg As Range ' Current Region Range
    Set crrg = ws.Range("B6").CurrentRegion
    Dim srg As Range ' Sort Range
    Set srg = crrg.Resize(, crrg.Columns.Count - 1).Offset(, 1)
    Dim krg As Range ' Key Range
    Set krg = srg.Rows(srg.Rows.Count) ' bottom-most row
    Debug.Print srg.Address, krg.Address
    With ws.Sort
        With .SortFields
            .Clear
            .Add2 Key:=krg, SortOn:=xlSortOnValues, _
                Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange srg
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Thank you for your reply. For some reason, the code isn't sorting properly according to row 11 for me, the 238.63 comes before 238.56. Also, if there's other data (like headings) above row 6, it seems to affect how the code sets the sort range. Do you know how to fix this? — CoffeeAndTea, Aug 24 '21 at 16:38

Sort data with dynamic columns

1 Answers1

Sort Columns