My excel is experiencing an issue where various adjustments to the excel (data entered into cells, resizing columns/rows, etc.) causes some of the excel cells to "disappear", usually a few rows that are all grouped together. When I say "disappear", I mean that the text displayed in the cells disappear, and the borders around those cells disappear. The background color remains. See my screenshots in the attached word document (Figure 1 is how it should normally look, and figure 2 is with the "disappeared" rows). Sometimes, the disappeared cells will come back and stay back when I adjust the row height of the affected rows, but sometimes this will make the disappeared cells return for just a second, only to disappear immediately again. Sometimes scrolling to the top of the worksheet and back down will make the cells re-appear again. It seems hit or miss as to what will temporarily fix the problem, but I don't have a permanent fix.



In the particular instance of the screenshots, the disappearance was caused by entering the letter X in cell K187 (i.e. a cell that was unrelated to the disappeared cells).

My hunch is that the issue stems to conditional formatting, since in the past I had re-selected which cells my conditional formatting applied to and it fixed the issue immediately (in that past instance, the cells that the conditional formatting applied to had become convoluted through the addition of new rows to the excel over time, so I reselected which cells the formatting applied to as one large group of cells [e.g. D4:D50] and that solved the issue at that time). But this current excel in my attachment has brand new conditional formatting rules that I can't adjust any further, so I am trying to find the root cause. This excel uses conditional formatting in a few places (in the attached screenshots, conditional formatting is used on cell C181 to change text color, and is also used in all of the white colored cells in column E to change the text's color, cell's background color, and remove top and bottom cell borders when certain conditions are met).

I am currently using Excel 2013, however the same error occurred back when I used Excel 2007 a year or two ago.

Does anyone know what exactly is causing these cells to disappear, and what I can do to stop it from happening?

Attached Files

Excel Disappearing Cells screenshots.docx (237.8 KB, 15 views)

If you haven't solved this yet, perhaps post an excel sheet ( no pics please)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I am attaching the actual excel, as this issue has not yet been resolved.

In order to try and initiate the error I have described, try placing an X in random cells of column K (the error appeared for me when I put an X anywhere between K26 and K37).

Attached Files

Disappearing Cells.xlsm (139.5 KB, 12 views)

The first condition in your CF is that any "x" placed in col K in an even row number will, turn the corresponding background of the cell in col E to gray and the text font color to the same shade of gray.
So, the background becomes gray, borders disappear ( they are not kept in the re-formatting) and text has the same color as the background making it "invisible"

Change the fill colors and/or font colors of the CF to solve it ( and eventually add borders to the CF formatting

Attached Images

Screenshot - 290216 - 18:10:05.png (58.1 KB, 18 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Pecoflyer,

That CF is entirely intentional (apologies if I did not make that clear). I do want the white-colored and black-bordered cell E8 to "disappear" when I mark that item as NA in cell K8 (for example). Cell E8 houses a due date, but when the item in H8 is Not Applicable, no need for the due date, so E8 should disappear. However this CF is restricted only to column E disappearing when column K in the same row has an X in it. There is nothing in that CF rule that would make any other cell in any other row disappear.

My issue is that when I enter info into cells (putting an X in the K column is just one example), MULTIPLE ROWS of cells disappear (see example in my initial screenshot attachment if you cannot duplicate the error in the actual excel), and there is no CF that states to do that. And these multiple rows that disappear are random as to which rows they are in relation to the cell I entered data into. Sometimes it will be a row I entered data into as well as the above and below few rows, sometimes it will be 4 rows above where I entered data, but it is definitely not due to a CF rule I explicitly set (although it could be a bug or unintended consequence of a CF rule I set).

I put an x in each cell from K26 to K37 and all appeared normally.

Its unfortunate you can't duplicate the issue.

Can anyone visually recognize the issue from my screenshot in the initial post?