Find last column & sort on last column

[Heather_Llo]

Hello,

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.

 

[Joe4]

You can get the last column number like this:

LastColumn = Cells(1, Rows.Count).End(xlToLeft).Column

Or if it is all continuous, you could just use Current Region with a range variable, i.e.

Dim mySortRange as Range
Set mySortRange = Range("A1").CurrentRegion