Find last column & sort on last column

Question

I need Excel to detect the last column I have and sort on that column. I have a macro that generates a new column every time it is used so I cannot use a constant.

Sub sortyness() 
Dim sortdata(A1 & ":", Cells(LastRow, LastColumn)) As Range

ActiveWorkbook.Worksheets("Compiled").Sort.SortFields.Clear    
ActiveWorkbook.Worksheets("Compiled").Sort.SortFields.Add _    
    Key:=Range(Sorton), Sorton:=xlSortOnValues, Order:=xlAscending, _    
    DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Compiled").Sort    
    .SetRange Range(sortdata)    
    .Header = xlYes    
    .MatchCase = False    
    .Orientation = xlTopToBottom    
    .SortMethod = xlPinYin    
    .Apply    
End With

End Sub

Here's a screenshot of the sheet:

I am having trouble with getting it to sort by the last column. Can I define the column by looking for the first cell in row 1 that has no data and then use that as a basis to sort? How should I modify my VBA?

Thank you.

I don't know how to edit this thing to get it to not appear as a duplicate, but it's obviously not a duplicate. Mine is more concerned with running a macro on the last column than it is finding the last column.

Answer 1

Sort the "last" column with a single line of VBA:

    Columns(ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count)._
        Column).Sort key1:=Columns(ActiveSheet._
        UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column)

...which is exactly the same as:

Sub SortLastColumn()
    With ActiveSheet.UsedRange
        Columns(.Columns(.Columns.Count).Column).Sort key1:=Columns(.Columns(.Columns.Count).Column)
    End With
End Sub

Answer 2

An vba sort operation actually requires much less code than you get from a recording.

Dim sortdata As Range, LastRow as long, LastColumn as long

With ActiveWorkbook.Worksheets("Compiled")
    LastRow = .cells(.rows.count, "A").end(xlup).row
    LastColumn = .cells(1, .columns.count).end(xltoleft).column
    with .range(.cells(1, 1), .Cells(LastRow, LastColumn))
         .Cells.Sort Key1:=.Columns(.columns.count), Order1:=xlAscending, _
                     Orientation:=xlTopToBottom, Header:=xlyes
    end with
end with

Answer 3

Concerning the second line here:

Dim sortdata(A1 & ":", Cells(LastRow, LastColumn)) As Range

This is not how you assign range. If you want to assign a range, starting on A1 and ending on lastRow, lastColumn, consider this:

Public Sub TestMe()

    Dim lastRow As Long: lastRow = 5
    Dim lastCol As Long: lastCol = 10

    Dim sortData As Range
    Set sortData = Range("A1:" & Cells(lastRow, lastCol).Address)

    Debug.Print sortData.Address

End Sub

In the above case the range is assigned to the ActiveSheet, which is not always what you may need. If you want to avoid assigning to the ActiveSheet, you should specify the worksheet as well:

With Worksheets("Compiled")
    Set sortData = .Range("A1:" & .Cells(lastRow, lastCol).Address)
End With

The two dots in the code above .Range and .Cells will make sure that you refer to the Worksheets("Compiled") and thus will save some problems in the future.