Macro sort buttons not working as planned

[sharpeye]

Hi everyone.
Oh I'm having some fun with this.
I have a worksheet that has new data added every day. I then sort the data into 5 different orders. Origonally I had the same data in 5 different sheets, each sheet with a different sort. Every day I was adding the new data to all 5 sheets and then running the sort on each sheet with the new data included.
Day before yesterday I decided I could save on the filesize by instead of having 5 seperate sheets, I can have one sheet and use 5 different sorts all having their own button.
To do the sort to capture all of the data, I click on the corner between A and 1, set up the sort criteria, check the my data has headers box and click OK and it works like a dream, all 5 buttons, to perfection.

Or so I thought.

When I ran the Macro for each button, pressing in the top left corner highlights the entire sheet, when I clicked on sort>data it highlighted all of my data, down to bottom of the data which was row 9992, what I didnt realise was when I now run the sort with the new data included it still uses 9992 as the last row so if I add another 100 rows in at the top and sort, this new sort doesnt include the 100 rows at the bottom of the sort.

Its a major problem as once I use each button I can then manually run a sort using the top corner and it keeps the sort criteria for the button I've just used but it seems a little long winded to have a button do the sort and then manually do the sort again.

What I need is instead of the range ending with a defined row, I could do with it being last row containing data, which is what it always does when I do it manually.

Im using Excel 2019

Thanks you in advance

Here is the Marco from one of my buttons.

Sub AK_Sort()
'
' AK_Sort Macro
'

'
Cells.Select
ActiveWorkbook.Worksheets("League_order").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("League_order").Sort.SortFields.Add(Range("H2:H9992") _
, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(112, _
48, 160)
ActiveWorkbook.Worksheets("League_order").Sort.SortFields.Add(Range("H2:H9992") _
, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
0, 0)
ActiveWorkbook.Worksheets("League_order").Sort.SortFields.Add2 Key:=Range( _
"AK2:AK9992"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("League_order").Sort.SortFields.Add2 Key:=Range( _
"K2:K9992"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("League_order").Sort.SortFields.Add2 Key:=Range( _
"I2:I9992"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("League_order").Sort
.SetRange Range("A1:BN9992")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Anthony47]

I think you may replace those "9992" with "100000" at it will work correctly
Try...

 

[sharpeye]

Thanks for your reply but unfortunately that wont work. My data has borders and coloured cells and including any cells that dont have this in a sort makes a mess, I get rows of data with no borders and rows or coloured cells with no data

 

[Anthony47]

So you need to calculate which is the last used row in your data, then replace the fixed row with the calculated one.
I.e. not Range("H2:H9992") but Range("H2:H" & LastRow)

 

[sharpeye]

The last row changes every day as I add new data on a daily basis

 

[Anthony47]

You have to calculate it within the macro.
For example:

Dim LastR as Long
On Error Resume Next
LastR = Range("A:BN").Find(What:="*", After:=Range("A1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
On Error GoTo 0
'
Cells.Select
ActiveWorkbook.Worksheets("League_order").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("League_order").Sort.SortFields.Add(Range("H2:H" & LastR) _
, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(112, _
48, 160)
'your code
'your code
'...
'

 

[Anthony47]

Beware I edited the message while you was reading it

 

[sharpeye]

thanks for the warning lol
I'll give a try when I get back home. Thanks for your help

 

[sharpeye]

Hi Anthony, I just tried the code you sent and it works

Thank you ever so much for your help

 

[Anthony47]

Thank you for the feedback