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: <img src="https://i.stack.imgur.com/kq3iS.png" alt="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?
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.
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
Concerning the second line here:
Dim sortdata(A1 & ":", Cells(LastRow, LastColumn)) As Range
This is <strong>not</strong> 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
.Cells will make sure that you refer to the
Worksheets("Compiled") and thus will save some problems in the future.
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