Caught in a loop that errors

[wwoods]

Thank you in advance for looking at this file and providing any assistance. I have a file with VBA designed in Excel 2010. I upgraded to Office 2016 and started getting an error Method 'Range' of object'_Global' failed when I try to sort from the waterfall page. I am far from expert and have walked through this a few times but not sure why.

[Norie]

How are you doing the sorting?

[wwoods]

Thanks for your reply. I have 4 option buttons that allow it to sorted by Airport, Type, Pub date, or alphabetically. depending on the button selected, the sub sorts by that criteria from a data page.
It then cuts and refers to watefall page and cuts and paste the data in that order on the waterfall. The portion that errors is show below.
'find bottom row on waterfall

With Sheets("Waterfall")
lastspot = .Cells(.Rows.Count, "A").End(xlUp).Row
newtop = lastspot + 1
Range("A8", "AV" & lastspot).Select
Selection.Cut
Range("A" & newtop).Select
ActiveSheet.Paste
Application.CutCopyMode = False
newbottom = .Cells(.Rows.Count, "A").End(xlUp).Row
spot = 8

For Count = 1 To UBound(SortOrder, 2)
Counter = newtop
Do Until Range("I" & Counter).Value = SortOrder(1, Count)

Counter = Counter + 1
Loop
movespot = Counter - 2

Range("A" & movespot, "AV" & movespot + 11).Select
Selection.Cut
Range("A" & spot).Select
ActiveSheet.Paste
Application.CutCopyMode = False
spot = spot + 12
Next

[Norie]

The reason that code errors is because Counter reaches 1048577 and there are only 1048576 rows on an Excel worksheet.

Why it Counter reaches that value I'm not 100% sure, is it a particular option button that's causing the problem?

[wwoods]

It is weird. It does not matter which button I use to sort. I noticed that when the error happens the sorting happens on the Data (hidden) worksheet rather than the waterfall. That seems to cause the error. Why it does not get back to the waterfall page and sort I do not understand. If I take the time to move the information on the data page back to the table it will run correctly. Occasionally it will sort correctly on its own. I am perplexed.

[Norie]

I only had a quick look at your code yesterday but I seem to recall that there were quite few unqualified range references.

When a range reference is unqualified it will refer to the active sheet and that could explain the problem, and why moving data seems to fix things.

The 'proper' way to avoid an error like this is to make sure you qualify things properly.

I'll take a closer look at the code later and try and post something to show what I mean.

[wwoods]

Thank you for your help.