I have a spreadsheet that is updated weekly by adding a two new columns of
figures.
How can I add a command button that will automatically sort by the last
column? I can record a macro to sort by the last column, but then when I add
data the last column is not the last column anymore (obviously, you may
say!). Also, occasionally when updating, a new row is added to the bottom.

Sub polly()
coll = Cells(1, Columns.Count).End(xlToLeft).Column
colletr = Split(Cells(1, coll).Address, "$")(1)
Columns("A:" & colletr).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range(colletr & "2"), Order1:=xlAscending, Header:=xlYes
End Sub

Here we assume that row #1 is a header row. coll will be a number
corresponding to the last occupied column. colletr will be the letter
corresponing to that last occupied column
--
Gary''s Student - gsnu200791


"Polyanna" wrote:

I have a spreadsheet that is updated weekly by adding a two new columns of
figures.
How can I add a command button that will automatically sort by the last
column? I can record a macro to sort by the last column, but then when I add
data the last column is not the last column anymore (obviously, you may
say!). Also, occasionally when updating, a new row is added to the bottom.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRngToSort As Range
Dim LastRow As Long
Dim LastCol As Long
With Me
'I used column A to find the last used row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'I used row 1 to find the last used column
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set myRngToSort = .Range("A1", .Cells(LastRow, LastCol))
End With

With myRngToSort
.Sort key1:=.Columns(.Columns.Count), order1:=xlAscending, _
header:=xlYes
End With

End Sub

Polyanna wrote:

I have a spreadsheet that is updated weekly by adding a two new columns of
figures.
How can I add a command button that will automatically sort by the last
column? I can record a macro to sort by the last column, but then when I add
data the last column is not the last column anymore (obviously, you may
say!). Also, occasionally when updating, a new row is added to the bottom.

--

Dave Peterson