Slow to select or edit cells

[alice71460]

I have the problem of openoffice calc spreadsheets becoming extremely slow to edit. Just moving between cells using the right arrow key can take up to a minute, or the program may stop responding.

This occurs using multiple versions of openoffice and OS:
OpenOffice 3.2 / Windows XP 32-bit Pro
OpenOffice 3.3.1 / Windows 7 64-bit Ultimate
OpenOffice 3.4.1 / Windows XP 32-bit Pro

Troubleshooting steps so far:
1) It is specific to one file. (new files and other files are OK)
2) If I save the file as .xls format, Calc still has a problem opening and editing but Microsoft Excel does not.
3) User Profile is not the problem (tried new profile, does not help)
4) I have also tried the suggestion of changing the Memory settings but this does not help.
5) Size of the file not an issue. I tried cutting down the file by removing half of the tab and it's still a problem. A previous version of the same file, which is still large does not exhibit the problem.
6) It seems to be related to a combination of specific data and formatting. Some rows are Auto color, some are blue or red. If I remove the color formatting and change all rows to Auto-color, suddenly I can edit the file easily. But, the previous version of the file also has color formatting, and I can edit it easily, so it has to be related to a combination of data and format.
EDIT: 7) Selecting all cells->Delete all objects had no effect. Resulting file is binary equal (there were no objects to delete)

So, there is some existing bug in OpenOffice that happens when you have color formatting and specific data. The spreadsheet is very large, with over 3000 rows and 18000 cells. I am trying to determine by elimination which data causes the problem. but it takes so long to close the non-responsive Calc program each time that this could take days.

[Villeroy]

Select all cells
Edit>Delete Contents...
Delete all objects

[alice71460]

Sorry, I guess I forgot to add some steps I had tried. I did try this step. I think there were no objects, as the resulting file is binary equal to the original file.

[Villeroy]

Select all cells
Format>Default Formatting

[lino]

Try what did work for me; export as .csv (you can inspect .csv file easily to find any weird things - just open it in text editor) and import back again.
I presume that file became slow after editing in Excel.
As general rule, Villeroy pointed, Format > Clear Direct Formatting and use styles instead.

[alice71460]

Yes, export as .csv and import back in *does* work. However, I lose all the formatting. I found that all I have to do is to remove the font color (set to Auto color) and the slowness is gone. The file is saved as .ods and so has never been edited in Excel. I found a post by another person who had a similar issue, and the advice he got was to stop using color and/or use a database instead.

Is Calc really not able to handle font color well in large files? The file has only text entries. There are no formulas or links. There are Unicode characters, as some entries are in Russian or Japanese.

[lino]

Glad you have solved it. When I had this kind of problem, it occurred on OOO and LO, but it was ok on older SOO (last SOO, I think 3.1). So, is it a bug or enhancement, but it was not like this forever.

Anyway, the sooner you start using styles, the better you'll see how good it is compared to direct formatting.

[alice71460]

So, using styles might be better? I have not used styles in Calc yet. I know I had some issues with styles using Writer. I wonder if it's similar to HTML where using CSS makes everything simpler. I guess I need to rebuild the spreadsheet--it's my daughter who updates it normally.

[Villeroy]

Formatting is completely unimportant. If you have anything to highlight, visualize, clarify, point out, then a couple of styles together with some conditional formatting is by clearer than the usual "organically grown" Excel sheet with thousands of different formattings.

If you want to see what I mean:
Download http://user.services.openoffice.org/en/ ... p?id=11048
Do NOT unzip and point Tools>Extensions... to this zip package.
Close the office or all spreadsheets at least.
Now you have a tool to select all combinations of different formattings (and contents as well).
Tools>Add-Ons>SpecialCells>Formattings...