Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Deleting all values in a column is slow.

I have a Calc spreadsheet (LibreOffice version 6.1.0.3, Windows 7) with a single worksheet. The worksheet comprises 8 columns and 2925 rows. Each column is a named range.

I am trying to clear all the values in a single column ("Duplicates") by using either:

ThisComponent.Sheets("Recordings").getCellRangeByname("Duplicates").clearContents(1 Or 4)

or

ThisComponent.getSheets().getByName("Recordings").getCellRangeByPosition(RangeColDuplicates, 1, RangeColDuplicates, LastRow+1).clearContents(1 OR 4) 'RangeColDuplicates = 7.

Either method deletes the values, but it takes approximately 4 seconds to delete the values.

If I select all columns, the process is almost instantaneous. If I select a number of columns between 1 and 7 using, for example:

ThisComponent.getSheets().getByName("Recordings").getCellRangeByPosition(0, 1, 3, LastRow+1).clearContents(1 OR 4)

The delete process takes less time than deleting a single column, but more than deleting all columns. It seems that the closer I approach to deleting all columns, the less time it takes.

Is this normal? The same process in Excel, using an equivalent spreadsheet, is almost instantaneous regardless of the number of columns selected.

Any help in speeding up this delete process would be appreciated.

Deleting all values in a column is slow.

I have a Calc spreadsheet (LibreOffice version 6.1.0.3, Windows 7) with a single worksheet. The worksheet comprises 8 columns and 2925 rows. Each column is a named range.

I am trying to clear all the values in a single column ("Duplicates") by using either:

ThisComponent.Sheets("Recordings").getCellRangeByname("Duplicates").clearContents(1 Or 4)

or

ThisComponent.getSheets().getByName("Recordings").getCellRangeByPosition(RangeColDuplicates, 1, RangeColDuplicates, LastRow+1).clearContents(1 OR 4) 'RangeColDuplicates = 7.

Either method deletes the values, Attached are 2 files:

"Delete Cell Contents.ods" contains the data I am having trouble with. I have stripped out all extraneous macro code and a dialog.

Column H of "Delete Cell Contents.ods" contains the data of column A without the duplicate values.

"Delete Test1.ods" is a file using dummy data but it using the same macro code as "Delete Cell Contents.ods".

I have created a toolbar ("Clear Cell Content") in each file to access the macros.

Running any of the macros in "Delete Test1.ods" results in almost instantaneous deletion of data in column H, while doing the same in "Delete Cell Contents.ods" takes approximately about 4 seconds to delete the values.

If I select all columns, the process is almost data in column H.

Using <ctrl +="" z=""> to undo the delete in "Delete Test1.ods" takes about 8 seconds, while in "Delete Test1.ods" the undo is instantaneous. If I select a number of columns between 1 and 7 using, for example:

ThisComponent.getSheets().getByName("Recordings").getCellRangeByPosition(0, 1, 3, LastRow+1).clearContents(1 OR 4)

The delete process takes less time than deleting a single column, but more than deleting all columns. It seems that the closer I approach to deleting all columns, the less time it takes.

Is this normal?

The same process in Excel, using an equivalent spreadsheet, is almost instantaneous regardless of the number of columns selected. delay is evident when selecting and deleting the data in column H manually.

"Delete Cell Contents.ods" has some direct cell formatting: vertical alignment is "top", horizontal alignment is "left" and "wrap text" is enabled. Removing this formatting speeds things up slightly.C:\fakepath\Delete Cell Contents.ods(/upfiles/15368529059303209.ods)(/upfiles/1536852887630988.ods)

Any help in speeding up this delete process would be appreciated.

Deleting all values in a column is slow.

I have a Calc spreadsheet (LibreOffice version 6.1.0.3, Windows 7) with a single worksheet. The worksheet comprises 8 columns and 2925 rows. Each column is a named range.

I am trying to clear all the values in a single column ("Duplicates") by using either:("Duplicates").

Attached are 2 files:

"Delete Cell Contents.ods" contains the data I am having trouble with. I have stripped out all extraneous macro code and a dialog.

Column H of "Delete Cell Contents.ods" contains the data of column A without the duplicate values.

"Delete Test1.ods" is a file using dummy data but using the same macro code as "Delete Cell Contents.ods".

I have created a toolbar ("Clear Cell Content") in each file to access the macros.

Running any of the macros in "Delete Test1.ods" results in almost instantaneous deletion of data in column H, while doing the same in "Delete Cell Contents.ods" takes about 4 seconds to delete the data in column H.

Using <ctrl +="" z=""> to undo Undoing the delete in "Delete Test1.ods" takes about 8 seconds, while in "Delete Test1.ods" the undo is instantaneous.

The same delay is evident when selecting and deleting the data in column H manually.

"Delete Cell Contents.ods" has some direct cell formatting: vertical alignment is "top", horizontal alignment is "left" and "wrap text" is enabled. Removing this formatting speeds things up slightly.

C:\fakepath\Delete Cell Contents.ods(/upfiles/15368529059303209.ods)(/upfiles/1536852887630988.ods)

Any help in speeding up this delete process would be appreciated.

Deleting all values in a column is slow.

I have a Calc spreadsheet (LibreOffice version 6.1.0.3, Windows 7) with a single worksheet. The worksheet comprises 8 columns and 2925 rows. Each column is a named range.

I am trying to clear all the values in a single column ("Duplicates").

Attached are 2 files:

"Delete Cell Contents.ods" contains the data I am having trouble with. I have stripped out all extraneous macro code and a dialog.

Column H of "Delete Cell Contents.ods" contains the data of column A without the duplicate values.

"Delete Test1.ods" is a file using dummy data but using the same macro code as "Delete Cell Contents.ods".

I have created a toolbar ("Clear Cell Content") in each file to access the macros.

Running any of the macros in "Delete Test1.ods" results in almost instantaneous deletion of data in column H, while doing the same in "Delete Cell Contents.ods" takes about 4 seconds to delete the data in column H.

Undoing the delete in "Delete Test1.ods" takes about 8 seconds, while in "Delete Test1.ods" the undo is instantaneous.

The same delay is evident when selecting and deleting the data in column H manually.

"Delete Cell Contents.ods" has some direct cell formatting: vertical alignment is "top", horizontal alignment is "left" and "wrap text" is enabled. Removing this formatting speeds things up slightly.

C:\fakepath\Delete Cell Contents.ods(/upfiles/15368529059303209.ods)(/upfiles/1536852887630988.ods)

C:\fakepath\Delete Cell Contents.ods (/upfiles/15390892797953911.ods)

Any help in speeding up this delete process would be appreciated.