Multi level sorting where header is in the 3rd row

[exce101]

Hi All,

I have dynamic data in my worksheet (named "Setup" currently but could change) which I am looking to apply a multi-level sort to.

The current dataset starts from A4 and ends at AD37 however this could also change as more data in rows and columns is added. The headers are in the third row while the first two rows are blank to house the macro buttons.

Below is the code I am currently using but need to tweak it to specify that the headers are in the second row rather than the first row.

Any help is greatly appreciated.

Sub MultiLevelSort()

Worksheets("Setup").Sort.SortFields.Clear
'Worksheets("Setup").UsedRange.Sort Key1:=Range("b3"), Key2:=Range("C3"), Key2:=Range("D3"), Header:=xlYes, _
'Order1:=xlAscending, Order2:=xlAscending, Order3:=xlDescending

End Sub

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

specify that the headers are in the second row

You said third row earlier & in your thread title, so I will assume third.

I would be a little nervous about relying on UsedRange when there are (currently) blank rows at the top. My suggestion would be something like this.

Sub MultiLevelSort_v2()
  With Sheets("Setup")
    With Intersect(.UsedRange, .Rows("3:" & Rows.Count))
      .Sort Key1:=.Columns(2), Order1:=xlAscending, _
            Key2:=.Columns(3), Order2:=xlAscending, _
            Key3:=.Columns(4), Order3:=xlDescending, _
            Header:=xlYes
    End With
  End With
End Sub

 

[exce101]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


You said third row earlier & in your thread title, so I will assume third.

I would be a little nervous about relying on UsedRange when there are (currently) blank rows at the top. My suggestion would be something like this.

Sub MultiLevelSort_v2()
  With Sheets("Setup")
    With Intersect(.UsedRange, .Rows("3:" & Rows.Count))
      .Sort Key1:=.Columns(2), Order1:=xlAscending, _
            Key2:=.Columns(3), Order2:=xlAscending, _
            Key3:=.Columns(4), Order3:=xlDescending, _
            Header:=xlYes
    End With
  End With
End Sub

Thank you so much for the speedy response. This works perfectly well.

I suppose if I wanted to make the code worksheet name agnostic, I would replace with activesheet?

I have now also updated my Excel Version (365) and Platform (Windows)

 

[Peter_SSs]

Thank you so much for the speedy response. This works perfectly well.

You're welcome. Thanks for the confirmation.

I suppose if I wanted to make the code worksheet name agnostic, I would replace with activesheet?

Yes

I have now also updated my Excel Version (365) and Platform (Windows)

Thanks for updating (though looks like you actually missed the bold bit )

 

[exce101]

Thanks for updating (though looks like you actually missed the bold bit )

That’s strange … I’ve checked now, updated and saved. Hopefully it’s come through.

Thanks once again for your help.

 

[Peter_SSs]

All good now.