Excel limit to Sort

MarkO

New Member
Joined
Jul 7, 2003
Messages
11
Hi All.

We use a simple Excel file to store job numbers, orders, etc. The sheet is locked but has some simple VBA in it to allow users to modify the content. Add row, delete row, edit row, sort rows, etc.

The sort code has recently stopped working for any cells below row 5260. The Sort code sorts a range 15 columns wide by X rows. The number of rows grows as users add new jobs.

So the range is currently 15 columns x 5269 rows. It still sorts the range but not those rows below ~5260.

Any information on this would be greatly appreciated.

Cheers,

Mark.

BTW We all run Office 2016 through Office 365 but the sheet is 10+ years old as is the VBA code within.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
It sounds to me that the VBA sort range needs to be edited.
That being the case I'd suggest a small rewrite of that specific code:
eg
Code:
Dim LastRow as Long
LastRow=cells(rows.count,"A").end(xlup).row
and then change any value in that code from 5260 to LastRow.

It won't matter thereafter how many rows you have to sort, if my understanding is correct.

As an afterthought some of your code may contain something like Range("B2:X5260").
Those instances would need to be rewritten as Range("B2:X" & LastRow), or within the range to which the sort applies.
 
Last edited:

MarkO

New Member
Joined
Jul 7, 2003
Messages
11
Thanks Brian.

I have the range set such that when a user adds a row (via a button) it is always within the existing range, so the range just gets bigger.

The range is currently row 12 to 5295 so 5260 is within the range. Its this that makes me think I've hit some limit in Excel or VBA.

Sub Sort_DataRange()
ActiveSheet.Unprotect "syswsr"
Range("All_Data").Sort Key1:=Range("All_Data").Cells(2, 15), Order1:=xlAscending
ActiveSheet.Protect "syswsr"
End Sub

Cheers.
 

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
That doesn't make any sense.

The last row of your sheet is miles below your range.
What does Cells(2,15) hold on your sheet? Is that the value of "5295"?
Even that doesn't make sense.

What is the macro block that does the sort?
I suggest therein is what you need to alter.
I assume that you know how that may appear:
Code:
With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
.
It will be within that macro that you will have to alter the "5295" value.

If that is not the case, we will just have to persevere.
 

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
I have the range set such that when a user adds a row (via a button) it is always within the existing range, so the range just gets bigger.
I think that this is the code to address.
That said, how many macros run on this sheet?
 

MarkO

New Member
Joined
Jul 7, 2003
Messages
11
My apologies for taking so long to reply. Please don't take my slackness for any lack of appreciation!

Turned out the issue was in column 15 which is what the sheet sorts by. Because we use some weird job numbers that can start with a any numeral or character, column 15 uses the left function to return a consistent value to to sort by. All well and good but someone (probably my boss) had been unprotecting the sheet and manually inserting rows instead of using the Insert button (and associated code) at the top of the sheet. In doing this he would occasionally (somehow) not copy the cell with the Left formulae.

Another example of the simplest solution being the correct one.

Again, thanks for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top