We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Styles and Formats [closed]

asked 2012-06-09 08:05:55 +0200

dunbrokin gravatar image

updated 2020-07-29 11:47:25 +0200

Alex Kemp gravatar image

I have about over 200 different Excel_Condformats in my Styles and Formats in Calc....I cannot seem to batch delete them I seem to have to do each separate one by hand!...and even some of them reappear after being deleted!

How can I get rid of them for good and all in one go?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-20 01:02:10.244294

2 Answers

Sort by » oldest newest most voted

answered 2012-06-09 19:10:38 +0200

JohnSUN gravatar image

You can quickly delete all of these styles with a simple macro:

REM Remove the cell styles that begin with prefixStyleName
Sub removeStyles()
Dim oStyleFamilies As Variant
Dim oStyleFamilie As Variant
Dim aElementNames As Variant
Dim i%, count%
Const prefixStyleName = "Excel_CondFormat_"
    oStyleFamilies = ThisComponent.getStyleFamilies()
    oStyleFamilie = oStyleFamilies.getByName("CellStyles")
    aElementNames = oStyleFamilie.getElementNames()
    count = 0
    For i = LBound(aElementNames) To UBound(aElementNames)
        If Left(aElementNames(i), Len(prefixStyleName)) = prefixStyleName Then
            count = count + 1
    Next i
    MsgBox ("It was removed " + count + " style(s) with the names '" + prefixStyleName + "...'", 64, "Completed successfully")
End Sub

However, you must remember that you lose a set of conditional formats in the document. The conditions for the cells remain in the same form as in the original xls-file, but the design of the cells which correspond to these conditions will be "undefined"

edit flag offensive delete link more


Wow! Thank you for that...I can see how it works....but how and where do I run it?

dunbrokin gravatar imagedunbrokin ( 2012-06-10 00:31:20 +0200 )edit

Worked it out, thanks....that worked great!

dunbrokin gravatar imagedunbrokin ( 2012-06-10 01:15:32 +0200 )edit

answered 2013-01-16 00:41:55 +0200

alavarre gravatar image

updated 2013-01-16 00:47:28 +0200

Brilliant. Absolutely brilliant. Thank you thank you.

I moved from Windoze to Linux in 2002 and never looked back, except haltingly at my zillions of VB and VBA scripts. The OOffice attempt at a macro language was just 'way too arcane. This LibreOffice Basic version seems much better, but still not a match, unless I can find a really good tutorial, which I have not yet. Probably the most pressing need is for a keyboard recording macro, which is basically how I learned VB. Record a series of actions, then analyse them and now you know it, rather than spending hours wading through man pages...

Excellent solution. Thank you again, so much.

Kind regards, Andy

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2012-06-09 08:05:55 +0200

Seen: 555 times

Last updated: Jan 16 '13