Excel freezes when expanding group of rows with conditional formatting

3
votes

Issue: Excel freezes when expanding group of rows which contain conditional formatting. I can save using CTRL + S, but worksheet functionality and toolbar buttons lock up.

Working solution: Deleting the conditional formatting, which consists of:

  • Rule: =AND($C7<>"",$C7<>0)
  • Applies to: =$C$7:$AB$84

Excel version: Excel 2016

File Type: .xlsb

Background details: Rows were initially grouped with a macro. VBA code which groups the rows:

ws.Range(ws.Cells(firstRow, 1), ws.Cells(lastRow, 1)).Rows.Group
ws.Outline.ShowLevels RowLevels:=1

Screenupdating is set to True. There are no other row groups. Beyond failure to expand the group, I have not come accross any other issues with the file.

How can I prevent this without deleting the conditional formatting?

2
vbaexcel

2 Answers

1
votes

I got a similar bug with Excel 2013 (15.0.4569.1504) MSO (15.0.4719.1000) 32 Bit.

The bug could be reproduced as such :

  • Expand a group so that the new rows will exceed the bottom of the screen and force the screen to scroll up.
  • Similarly, unhide rows in the same conditions.

I was able to find several other workaround :

  • Change the height of the first row
  • Remove "Heading" (from Ribbon View - Heading).
  • Remove Those 3 options TOEGETHER (from option - advanced).
    • Show horizontal scroll bar
    • Show vertical scroll bar
    • Show sheet Tabs
  • Change Screen zoom
  • UnFreezing panel
  • Delete ALL conditionnal formating
  • Use another computer.

The following actions had no impacts :

  • Groups are not involved, unhiding rows is the key :

    • reduce nesting group level => Freeze
    • Delete all groups and rebuild them => Freeze

  • Other graphical components are not involved :

    • Delete comments => Freeze
    • Delete VBA components (buttons) => Freeze
    • Clean unused format (with XlStyleTool) => Freeze
    • Unmerge cells => Freeze

  • One single conditional formatting leads to the problem :

    • Delete some conditional formatting => Freeze
    • changing condition formulas => Freeze
    • changing option (stop if true) => Freeze
    • changing format => Freeze
    • Delete all conditional formatting, save file under another name and create one new conditional formatting => Freeze

  • File format is not involved :

    • Saving the file to Excel 2003 format and use it in compatibility mode. => Freeze
    • Copy the sheet to another workbook. => Freeze
    • Using page Break view instead of normal. => Freeze

  • Disable Hardware Acceleration => Freeze

  • Disable Add-in => Freeze

My conclusion is that it is a graphical bug linked to my video driver in very specific conditions. I just increased the first row size which fixed the problem.

My graphical card and drivers are :

  • AMD Radeon HD 7400M Series - BIOS Version 013.009.000.010
  • Catalyst Version 15.7.1
  • Windows 7 - 64 Bit - Sp1
0
votes

I had the same issue in one particular sheet. My workaround was set the same font for all the sheet (font and size)