Heather_Llo
New Member
- Joined
- Feb 16, 2018
- Messages
- 17
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.
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 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.
Here's a screenshot of the sheet.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
![2018-03-12_1044.png](/board/proxy.php?image=https%3A%2F%2Fcontent.screencast.com%2Fusers%2FNotSkip%2Ffolders%2FJing%2Fmedia%2F28e33cde-2acb-4c79-9ebb-9b7dd1fa6d35%2F2018-03-12_1044.png&hash=629581ba184aa42e9ea4aad568b0af06)
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.