Excel limit to Sort

[MarkO]

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.

 

[BrianJN1]

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

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.

 

[MarkO]

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]

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:

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]

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]

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.