Find an error in conditional formatting

[Robyn]

Hi all,

I have a spreadsheet in which I've occasionally wanted to insert a column, but got an error message: "To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet." I never knew what nonblank cells it was talking about. Today, when messing around with the conditional formatting in some of the cells, I figured it out. Some formatting refers to a table. I should have made the references absolute. Example: $N$1.$R$10. Instead some were not absolute, and in the process of copying them around, some wound up with a range going all the way to the last column. Example: $N$1.IV10. That being the case, Excel would think all the columns were in use, so it had no room to insert one.

I went through the spreadsheet and fixed all such cases that I could find, then triumphantly attempted to insert a column. Well, guess what. Still got the error message! I really hate the thought of looking through every cell manually. The spreadsheet is a calendar. Actually what I've done so far is only January, and thought I had fixed it, but evidently not. If I get January to work, then I'm still facing 11 more months with up to 31 days each. So, does anyone know a way to easily see all the conditional formats? I am running an old Excel, Office 2003. Any suggestions will be most appreciated.

Thanks!

Robyn

 

[rollis13]

Have you tried deleting all the columns to the right of your last used column ?

 

[Robyn]

I have deleted them all many times. (The spreadsheet is years old and has probably been revised hundreds of times.) I'm pretty sure that originally, there was no problem with inserting columns. I have no idea when the problem began. Anyway, deleting columns that seemed to be empty never did any good. When I found the error in one of the conditional formats, I thought that was the key to the problem. Since the format referred to the last possible column, Excel thought all the columns were in use. But fixing the formats did not solve the problem, so either (1) the formats are not actually the issue, or (2), they are, but I missed finding all the errors in them.

However, it occurs to me now that with or without formulas, we often do things that affect the entire spreadsheet. If you click on a column heading or a row number, and then tell Excel, for example, to center all the data in that column or row, then there's your instruction going all the way to the end. And yet, it doesn't prevent you from inserting a new column or row. Which means, back to Square One. Are the formats responsible for the problem, and if not, then what is?

 

[rollis13]

Just to be sure since english isn't my first language: I mean "delete" not "clear" columns. So, I have no other idea. Would it be a big job to copy only the used cells to a new book ?

 

[Robyn]

Since I've actually "deleted" them - they were in fact "cleared" at the same time. Since that didn't work, it seems the problem really is in some other cell which REFERENCES those columns. That's why I was so happy when I found the error in the conditional formatting. Data on these sheets ends at around Column BA. (Varies slightly on some sheets.) But if I have a formula (including a format instruction) which refers to Column IV, then does Excel think there is data all the way to IV? But if I delete (or clear) everything in BB to IV, Excel immediately replaces them with new Columns BB to IV. Somewhere to the left of those columns, the formula still exists, and causes Excel to STILL think there is data in those referenced columns, even if it just recreated those columns. So, as we say in English, "****ed if you do, and ****ed if you don't."

Copying over the used cells to a new book would not work. The error is hiding somewhere inside the used cells. Copying those cells would just create the error again. I'm pretty sure, the only solution is to find what the error is inside those cells, and fix it.

 

[BlueHornet]

Are you certain that there are no Named Ranges that also refer to phantom tables, or containing relative references that shift them to the borders of the worksheet?

Have you used any utilities (such as FindLink.xla) to attempt to search within your valid data / tables to seek references to border columns and/or rows?

Finally, after deleting the unused columns (yes, Excel will re-create them, but there shouldn't be anything in them now), have you saved the workbook, closed it, and re-opened it? I've noticed in the past that when I have used extra rows and columns (such as for ad hoc testing of formulas, for example, or temporary data tables to test references, lookups and the like) that End-Home will continue to seek out "what used to be" the bottom right corner of the worksheet, unless I delete those rows and columns, highlight the entire range of "valid data" on the worksheet, then save. After doing all of that, the End-Home reference is reset to where it should be.

In addition to all of the foregoing, have you had any buttons, text boxes or other graphics, uncleared filters, merged cells or other anomalies that could be remaining when you think all of your unused columns are deleted?

 

[Robyn]

It is SOLVED! Blue Hornet, it turned out to be exactly what you said - deleting all those supposedly empty columns, and then SAVING the file. (I actually figured it out before seeing your post.) I didn't have to highlight the used cells. Simply saving the file and reopening it, immediately after deleting the empty columns, did the trick.

At first I thought the conditional formats going all the way to Column IV really were the culprits, since I had already fixed them all in January, then deleted the columns, then saved the file, closed it, and reopened it, and voila! - problem solved.

So then I did the same with February.

Then, with March, I got bolder - deleted the unused columns, saved the file, closed it, and reopened it, but NEVER FIXED the conditional formats. Worked anyway. So those formats were never the problem.

What I don't understand is, over the years I have periodically tried to fix this matter of inserting new columns. Strangely enough, sometimes I could insert new columns, sometimes I couldn't. (I never attempted to analyze whether this occurred only for some months, that is, only on some tabs of the spreadsheet.) However, in cases where I deleted all the empty columns but still couldn't add new columns to the left, even though I didn't IMMEDIATELY save the file and reopen it, of course the file did not stay open ever since. At the end of the day, it got closed. On another day, it got opened again. At that moment, shouldn't Excel have recognized the new end of the worksheet?

Anyway, one more problem solved, even if I don't quite understand how. At least I'll know what to do if such problem ever occurs again.

Thanks, both of you, for your help.