Clean empty cells from a worksheet

[jollytall]

I have many unused cells in the worksheet. I do not exactly know where they come from (probably in a large copy operation created even if in the source the cell did not exist), but I would like to clean them. The reason is that I try to use

Code: Pascal  [Select][+][-]

1. if not assigned(Worksheet.FindCell(Row, Column)) then

logic to skip unused cells, but sometimes they exist, so I use instead

Code: Pascal  [Select][+][-]

1. if Worksheet.ReadAsText(Row, Column) <> '' then

but I think it is lower performance.
Is there an easy way to clean unused cells?

[wp]

It is a tiny bit faster when you query the ContentType field of the cell record:

Code: Pascal  [Select][+][-]

1.  if (cell <> nil) and (cell^.ContentType = cctEmpty) then...

Note that when a cell contains a formula which results in an empty string (= empty cell) this instruction will be deleted as well. If you want to keep this formula you must also check "if not Hasformula(cell)".

In order to delete a cell with all contents, formatting, comments, hyperlinks, formulas and memory, call Worksheet.DeleteCell(cell). There is also a Worksheet.RemoveCell(cell) which does the same, but keeps the memory.

See the attached example. Compare the input file "test.xlsx" and the result after deletion, "test-emptied.xlsx".

[jollytall]

Thanks.
Did you really mean RemoveCell? There is a third version EraseCell and the description is basically the same, what you write for RemoveCell (i.e. deletes but keeps memory). Remove cell is in the protected section, so it cannot even be called directly.
What does it mean that "keeps memory"? If all references removed for a cell and the memory is still there, isn't it a memory leak? When should one use that?

Maybe a bit more explanation of the three versions and a how-to when-to use them would be good.

[wp]

Sorry, I meant EraseCell.

The protected RemoveCell is probably a left-over from an ancient version.

"deletes but keeps memory" means: deletes all the content and associated data (formatting, formulas, hyperlinks, comments), but keeps the cell record in the AVLTree. In other words: If you'd do a worksheet.FindCell, the cell would still be found but it would be empty.

[jollytall]

Thanks, it is clear now.