CODEWITHSUNDEEP
excelvba
xyz
xyz

Reputation: 2300

Auto sort to last column

Sorry about the flury of posting, I am trying to finish a project (there always seems to be one more thing)

I am tring to auto sort to last column starting at F2 I have the following but is not working

Thanks

Sub Sort()

Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet

Set ws = Sheets("sheet1")

lastRow = ws.Range("F" & ws.Rows.Count).End(xlUp).Row
lastCol = Cells(2, ws.Columns.Count).End(xlToLeft).Column

With Sheets("Sheet1")
    ws.Range(ws.Range("F2"), ws.Cells(lastRow, lastCol)).Sort _
            Key1:=Range("lastCol"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
End With
End Sub

Upvotes: 1

Views: 602

Answers (1)

Jon Crowell
Jon Crowell

Reputation: 22358

The value for Key1 must be a range. You are trying to use the number that is the last column, and that won't work even if you remove the quotation marks.

Replace Key1:=Range("lastCol") with Key1:=Cells(2, lastCol)

Note that you can use the GetColumnLetter function I included in my previous answer to get the letter for the lastCol column. If you have the letter, you can use this syntax instead of the Cells version: Key1:=Range(myCol & 2)

To make sure you know what you are sorting, you can add a little bit of debugging code. You can also use the Immediate window and the Watch window to figure this out.

Replace your entire sub with this:

Sub Sort()

Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet
Dim rng As Range
Dim sortRng As Range

Set ws = Sheets("sheet1")

lastRow = ws.Range("F" & ws.Rows.Count).End(xlUp).Row
lastCol = Cells(2, ws.Columns.Count).End(xlToLeft).Column

Set rng = ws.Range(ws.Range("F2"), ws.Cells(lastRow, lastCol))
Set sortRng = ws.Cells(lastRow, lastCol)
MsgBox "I will sort this range: " & rng.Address & _
           " using this column: " & sortRng

rng.Sort Key1:=sortRng, Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal

End Sub

Upvotes: 2

Related Questions