Using dynamic Key Range for sorting a table column

[Squreshi]

Hi, Apologies if i am making a mistake or not following some proper protocols to post here. I am new to the forum and new to VBA but I am a quick learner.

Need to ask if there is a way (without defining a variable) to replace the hard coded key range in the following code? I have a worksheet called "Sheet1" and a table in there defined as "Table1". So how do I use the listobjects/listcolumns to simply say that the key range is column("Col7") for example.

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("S2:S160"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

 

[mumps]

Try:

Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("S2:S" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

If you want to use column 7, change the range to Key:=Range("G2:G" & LastRow)

 

[Squreshi]

Thanks and grateful for the response. I have the table totals in the last row so was avoiding using the LastRow. I had the following code in place and it works perfect which uses the column name instead. This works fine on the sheet that I am working now, but if you feel there could be a problem in other sheets where the rows keep on changing, please let me know. The only difference is between how the Key Range is defined.

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Object1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Object1").Sort.SortFields.Add Key _
:=Range("Object1[ColumnName]"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Object1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[mumps]

If it's just a matter of excluding the last row which is the total, then you could try:

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1

 

[Squreshi]

Many Thanks for the helpful code. Grateful.

 

[mumps]

You are very welcome.