Ask Your Question

clearing formats in LO calc?

asked 2018-02-04 10:52:09 +0100

nic gravatar image

updated 2018-02-04 10:53:04 +0100

I have used a lot of conditional formatting. If I press F11 I get a list or around 250 formats. Around 200 of these are superfluous and I suspect evaluating them may be slowing the spreadsheet. How do I remove the unwanted formats [all are in the form eg: 'Excel_CondFormat_11_11_1']

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-02-04 14:41:31 +0100

JohnSUN gravatar image

Try this macro

Sub removeExcelStyles
Dim oStyleFamilies As Variant
Dim oStyle As Variant
Dim oElementNames As Variant
Dim i As Long 
Dim sTmp As String 
    oStyleFamilies = ThisComponent.getStyleFamilies()
    oStyle = oStyleFamilies.getByName("CellStyles")
    oElementNames = oStyle.getElementNames()
    For i = LBound(oElementNames) To UBound(oElementNames)
        If Left(oElementNames(i), Len("Excel_CondFormat_"))="Excel_CondFormat_" Then oStyle.removeByName(oElementNames(i))
    Next i
End Sub

And do not save your spreadsheet as an Excel file, use the ODF format - in this case, the new extra formats will not occur every time you save

edit flag offensive delete link more


Could not remember where I got this code from. Remembered!

JohnSUN gravatar imageJohnSUN ( 2018-02-05 10:08:08 +0100 )edit

answered 2018-02-05 11:30:49 +0100

nic gravatar image

Thanks for the code but the answer's simple: Load the .ods spreadsheet. In the list that appears when F11 is pressed highlight the unwanted items, rt mouse and select 'Delete'. [I'm sure I tried this before I posted the query, but it now works for some reason] TASK LIST: F11>highlight unwanteds>rt mouse>'Delete'

edit flag offensive delete link more


Yes, you are absolutely right - it's very simple. Now, that the Stylist allows to select more than one style. This macro was written when this was impossible :-)

JohnSUN gravatar imageJohnSUN ( 2018-02-05 12:42:35 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-04 10:52:09 +0100

Seen: 55 times

Last updated: Feb 05 '18