Expanding a set of grouped rows causing excel to freeze/crash

[akoo371]

In one of my workbook sheets, I have a section of about 10 rows that I've grouped together. When I go to ungroup the rows using the "+" sign, it freezes Excel -- BUT, it only freezes if I'm scrolled all the way over to the left of the sheet, and if Excel is maximized on my monitor, and I'm on my primary monitor. If any of these conditions is not met, the sheet works fine (i.e., if I'm even one column over -- not showing column A, or if I move Excel over to my second monitor, I can expand the group with no issues). 

I've never had this issue with any other workbook, and I'm completely stumped since it's such a narrow set of circumstances that causes this -- however, this set of circumstances is how I use excel and the sheet 99% of the time, so it's a big problem for me!

[#2]

@akoo371 

With your permission, if I can recommend you, add a file (without sensitive data) to your project.

It is much easier for someone who wants to help to understand the subject.

Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases, like this case :).

* Knowing the Excel version and operating system would also be an advantage.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3]

@NikolinoDE Thanks for your response. I've included an example. Recap of my problem: when i have the attached sheet open, fill my primary monitor with Excel, scroll all the way to the left of the "Grouping Issue" tab, and then click on the + sign on row 22 to expand the grouped rows, Excel freezes up. If I change one little thing about those conditions (i.e. open on second monitor, or not maximize the sheet on my primary monitor), I have no issues. It's a very strange set of circumstances, so i'm not sure if you will be able to replicate the problem on your computer. Thanks again for any help you can provide. 

[#4]

@akoo371 

I can't replicate it to myself.

Is the VBA code in the "Assumptions" worksheet wanted?

fade in and fade out is also possible manually.

Have you downloaded the latest video driver version?

With Windows Update, some PCs are very sensitive, although the PC manufacturers should have solved these problems long ago, it happens now and then that after a Windows update, some PCs behave problematically (would rather not mention any brands in a public forum 🙂

Which version of Excel and operating system do you have?

Sorry that I took your time and couldn't give you a quick solution.

Wish you a nice day.

Nikolino

I know I don't know anything (Socrates)

[#5]

@NikolinoDE Thank you so much for your help. 

I tried updating the display driver and that did not work. However, since my primary monitor is on the older side, I swapped it out with a newer one that I have and it resolved the issue! I had no idea that hardware could cause Excel to malfunction! Thanks again for your help, I greatly appreciate it!

[#6]

I had the same issue
Your remark about changing monitor pointed out for me that it is a monitor issue
Realizing that it is a monitor issue I could resolve it by changing a setting in Excel Options:
General -> User Interface Options (first block) -> select radio button "Optimize for compatibility" instead of "Optimize for best appearance".

[#7]

I had the identical issue this week on Excel 2019 on Windows 10 (Interestingly: the identical spreadsheet does not experience the problem on my iMac running Excel 2019 on Catalina). After 30 minutes I was close to replicating & eliminating the problem at will on Windows.   It was easier to eliminate the issue than triggering the issue to re-occur. The symptom "appears" to be a freezing excel app, but that is not what's actually happening. It's more of a visual zombie issue.... What's actually happening is that the Excel app is still running but it's "window painter" stopped updating the Excel window you are seeing; but the commands you are typing are still being registered by Excel. This notion is proved by several ways: you can expand the excel window and it will not repaint any of the expanded area, the task manager shows it's running (i.e., it's still responsive), you can update data within a cell, you can switch tabs, etc.  Then if you "save the zombie excel file" all these changes will be in the excel file once reopened; hence, be careful not to accidentally change data while in the visual zombie state. To eliminate the "visual zombie" issue, what I've found to work was: 1) open the spreadsheet but don't expand the grouped rows yet, 2) ZOOM out until all rows and columns are visible within the viewable window (to be extra safe, I had extra blank columns and rows to the right and below the data area); 3) Once fully viewable, now expand the grouped rows that would overlap the horizontal freeze line; and it should work find and the visual zombie should be gone for good.  4) Save the spreadsheet.

So, it appears having the entire data area viewable at the start (i.e., before expanding) allows EXCEL to learn the proper row expansion ruleset without corrupting its window painter; and once save the spreadsheet will behave thereafter, including zooming in/out to your proper viewing scale and saving at that zoom scale.  Once eliminated, I haven't figured out the exact triggering sequence [but I triggered it nearly a dozen different times with test spreadsheets], but the problem manifests itself only when a combination of several excel elements are involved: A) Horizontal Pane freeze is ON; B) Row grouping is ON; C) the expansion of the grouped rows MUST overlap the horizontal freeze line once the group is expanded; D) and [potentially] AutoFilter is ON the rows that get "overlapped"; E); Column Groupings are enabled; and F) All the columns are not within the viewable Excel window.   Play with this and you'll surely stumble into the visual zombie. Hope this helps

[#8]

@LADDCustom29 That worked a treat, thank you.

[#9]

Thank you for this!!! I had the same issue just now and was struggling to figure it out, zooming out did the trick (though I wasn't using freeze frames)

[#10]

^^^ THIS ^^^

[#11]

@LADDCustom29 Hi!

The investigation you've brought us regarding the Excel issue is extremely helpful. In our company, we deal with numerous files containing thousands of rows and dozens of columns. We only work with Horizontal Pane freeze ON in our files, and we frequently use row grouping to facilitate data reading. I'm new here, so I ask: would it be possible for us to bring this issue to be investigated by Microsoft? For almost 10 years (!), we've been experiencing this problem, and there has never been an apparent solution from Microsoft!

[#12]

@Dhcf1993 

That would be good, but I'm not really sure how to go about officially requesting a fix from Microsoft..  The issue has been around for years, although it doesn't manifest itself on the Apple version of Excel