FenceFurniture
17th August 2020, 11:47 AM
A few weeks or so ago I started getting the most odd behaviour in Excel - I don't think this was anything to do with an update, but I can't be sure.
Win 10, Office 2016.
I have a spreadsheet that has the top row and columns A-E frozen (so I click in F2 and freeze the panes). Here it is:
478929
It normally behaves as you would expect it to. In the top line I have a whole bunch of shapes that have macros linked to them. They are mostly "jump to" but one is for sorting and another is for a major review of fonts etc (it's a very large sheet with about 2500 highly formatted lines).I'll come back to this macro later....
It still scrolls as it should....until I paste a cell - any cell. What happens then is that as I scroll down, once I get to that cell then everything ABOVE the frozen line jumps around like crazy and everything BELOW it stays still until I stop scrolling. At that point the lines underneath catch up, but the top line is rolled halfway up so that I can usually not see the shapes that have the macros linked to them. If I scroll to the right then the first 5 columns get the jitters too.
Here it is caught mid-jitter (even line 2 "AUSTRALIAN" has jumped above the freeze line):
478930
If I scroll up to the top, there will be TWO occurrences line 2! One above the freeze line and one below it.
If I then unfreeze the panes, refreeze them, all is good again until I do another paste (other things probably affect it too, but unknown).
Referring back to that macro the checks and formats all the fonts: it used to take about 10-15 seconds to run, but now it takes 150 seconds, and I haven't increased the code and nor have I added an extraordinary amount of lines to the sheet.
Back in the day of Office 2003 you were only allowed to have 4000 formatted cells in a workbook. I don't know if there is a limit on that now but if there is it is certainly increased way above that. This is an extensive workbook, and this sheet alone would have in excess of 80,0000 formatted cells (at least 30 columns by 2680 rows). Could this odd behaviour be a result of hitting some limit? Or is it just the usual "FF's weirdo shight that nobody can explain"?
Maybe it might be suggested that I re-install Excel but that is a nightmare for me - weeks of work. Here is my highly customised Home Ribbon, and I have no idea on how to do this again. It took me a month to get it right when I changed to Office 16 in 2016.
478931
Win 10, Office 2016.
I have a spreadsheet that has the top row and columns A-E frozen (so I click in F2 and freeze the panes). Here it is:
478929
It normally behaves as you would expect it to. In the top line I have a whole bunch of shapes that have macros linked to them. They are mostly "jump to" but one is for sorting and another is for a major review of fonts etc (it's a very large sheet with about 2500 highly formatted lines).I'll come back to this macro later....
It still scrolls as it should....until I paste a cell - any cell. What happens then is that as I scroll down, once I get to that cell then everything ABOVE the frozen line jumps around like crazy and everything BELOW it stays still until I stop scrolling. At that point the lines underneath catch up, but the top line is rolled halfway up so that I can usually not see the shapes that have the macros linked to them. If I scroll to the right then the first 5 columns get the jitters too.
Here it is caught mid-jitter (even line 2 "AUSTRALIAN" has jumped above the freeze line):
478930
If I scroll up to the top, there will be TWO occurrences line 2! One above the freeze line and one below it.
If I then unfreeze the panes, refreeze them, all is good again until I do another paste (other things probably affect it too, but unknown).
Referring back to that macro the checks and formats all the fonts: it used to take about 10-15 seconds to run, but now it takes 150 seconds, and I haven't increased the code and nor have I added an extraordinary amount of lines to the sheet.
Back in the day of Office 2003 you were only allowed to have 4000 formatted cells in a workbook. I don't know if there is a limit on that now but if there is it is certainly increased way above that. This is an extensive workbook, and this sheet alone would have in excess of 80,0000 formatted cells (at least 30 columns by 2680 rows). Could this odd behaviour be a result of hitting some limit? Or is it just the usual "FF's weirdo shight that nobody can explain"?
Maybe it might be suggested that I re-install Excel but that is a nightmare for me - weeks of work. Here is my highly customised Home Ribbon, and I have no idea on how to do this again. It took me a month to get it right when I changed to Office 16 in 2016.
478931