67557

Find last column & sort on last column

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?

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.

Answer1:

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

Answer2:

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 .Range and .Cells will make sure that you refer to the Worksheets("Compiled") and thus will save some problems in the future.

Answer3:

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

Recommend

  • Need to find a way to loop this macro through every other column
  • VBA Sort Macro not working
  • excel vba replace failure
  • Excel - VBA : automatically replace words with another (using What/Replacement)
  • Excel VBA Replace with Array Groups
  • Sort without moving formatting
  • Why setOnCheckedChangeListener not working in a popup that uses LayoutInflater?
  • Sort macro and data validation macro
  • Deleting entire row on criteria cannot handle 400,000 rows
  • Disable tax programmatically for a specific user role
  • How to get from a tuple to a tuple of references to elements in the tuple?
  • Executing a function that adds columns and populates them dependig on other columns in Pandas
  • Unique pairs in R, ignoring order
  • Convert array into percentiles
  • Float Label hint (TextInputLayout) not works with Android Data Binding
  • Could be Text orientation of JTextArea changed by keyboard shortcut?
  • jQuery: click function bind in for-loop with closure fix
  • Is it possible to utilize $addToSet multiple times in the same update?
  • Easy Way to Get Averages Based on Names in List
  • Recreate Apple Watch fitness tracker ‘progress’ bar - gradient on CAShapeLayer stroke
  • Two column unique constraint ActiveAndroid
  • LibGdx GLES2.0 cube texel stretching
  • mysql and indexes with more than one column
  • Complex Silverlight TreeView, is nested hierarchy possible?
  • Multibinding Multiselection ListView
  • Use allowDiskUse in criteria query with Grails and the MongoDB plugin?
  • Python: Split a String Field into 3 Separate Fields using Lambda
  • to implement a spinner in angular2+
  • Scala: Function returning an unknown type
  • jQuery - resize an elements height to match window without refreshing, on window resize
  • Using MouseListener to select a range of cells in a grid
  • Reduction and collapse clauses in OMP have some confusing points
  • MongoDb aggregation
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • WPF Applying a trigger on binding failure
  • How to stop GridView from loading again when I press back button?
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs