0

Total disclosure - I am out of my depth here! I'm editing a macro a made with VBA editor and I need to get column W to sort by values highest to lowest. The "header" in column W starts at W8 and the first row of actual data is from W9 downwards. I do want to entire worksheet to resort just as it would have done had I used the filter arrow on the column header to o the same thing (presuming I had filter arrows on all the columns which I do). The thing is that this macro is to be run on an input template which gets updated once a month and every month new rows are added so I need to be able to make sure the sort gets applied from W9 to the last row of data (whatever that may be on that particular month).

I hardly dare post my awful attempt at the code - I just have something which I can confirm doesn't work!

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1345").Sort.SortFields. _
    Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1345").Sort.SortFields. _
    Add Key:=Range("Table1345[[#All],[Reporting_Period_Total]]"), SortOn:= _
    xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1345").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Caz
  • 1
  • 1
  • 1
    Please do post your code as that way you will get help. Don't be shy, there's no shame in not knowing how to do something. You can always record a macro to get the syntax right. – SJR Dec 04 '20 at 12:48
  • Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear Range("W9").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("W9"), _ Sort.SortFields.Add Key:=Range("W9"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range(Selection, Selection.End(xlDown)) .Header = xlGuess – Caz Dec 04 '20 at 12:56
  • I'm sorry it wouldn't let me post in the original request because apparently my code isn't formatted correctly and I'm so sorry I don't know what it needs to correct it. I just put the code here in the comments. – Caz Dec 04 '20 at 12:59
  • Thanks SJR! That worked. And I have just had a breakthrough! Check out my code - it works. Now I'm just concerned about future proofing it - there's just something about the list objects bit - I have a fuzzy memory of having to kick that out before because it interfering with me wanting to be able to fill down rather than follow selected data - am I being paranoid here? – Caz Dec 04 '20 at 13:16
  • Apparently I also can't express myself very well either! Sorry just read my last post back. I just had a feeling that list objects was specific to this dataset and will cause problems if more rows are added to the data in subsequent months – Caz Dec 04 '20 at 13:17
  • Using ListObjects is the way to go as a table expands as you add more data. Also helpful for you to read this https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba – SJR Dec 04 '20 at 13:24

1 Answers1

0

You can use Sort method of Range object. You can use up to 3 columns this way. In this example I assume the table's range is U8:W12:

Sub SortList()
    Dim list As ListObject
    Set list = Sheets("Sheet1").ListObjects("Table1345")
    With list.Range
        .Sort Key1:=.Cells(3), Order1:=xlAscending, Header:=xlYes '//Change order as needed
    End With
End Sub
JohnyL
  • 6,894
  • 3
  • 22
  • 41