Style problem - unable to delete it
Style problem - unable to delete it
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Style problem - unable to delete it

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a style in a large and complicated workbook (over 20 sheets) that I want to delete. Unfortunately it once was a Normal style in another workbook and I have been unable to delete it. I also have a "regular" Normal style that cannot be deleted.

    I am trying to clean up this workbook for distribution to others in our organization. There are sheets within the workbook that will be made a part of other workbooks. Getting rid of this unwanted style is not critical it is just one of those things that bug me. (we have all been there)

    Any help would be greatly appreciated.


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There's an ASAP Utilities | Format | Remove all unused styles... menu command installed by the free ASAP Utilities add-in. See www.asap-utilities.com.

    [ This Message was edited by: Mark W. on 2002-04-04 14:16 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Just a change will resetting format style to normal and modify button to catergory general.. if my memeory is any good will this not remove the now set style.. ie ste to defaut...


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, Thanks for the info on the ASAP Utilities. Installed them and I know I will find them useful but....even though I tried the "remove unused styles", the style is still there.

    This may have worked IF I could find where this style is being used. Have not been able to figure out a way to search for a style.

    Like I said Excel is protecting this style the same way it does the "Normal" style. Did just notice an added little quirk, the problem style has a space at the end of its name. I don't know if this makes a difference or not.

    Thanks

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    klb, what if you modified the "offending" style to display some "eye-popping" pattern. Perhaps that would help you find where it was being used.

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Changed the color and font size so that it would jump out. The style is not being used. After running the asap utility I get a message to the effect that there were 13 styles and 12 are in use. The other 18 were removed. I know, the math doesn't make sense to me either.

    Saved and then closed the file. Reopened it and ran the utility again with the same results.

    Net result is the style is still there.


  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sounds like you might be dealing with a corrupt worksheet. What if you use Copy/Paste Special... to move the contents and formats to another workbook?

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Already tried that but.....

    There are almost 600 ranges named in the original sheet and each would have to be redefined. There is considerable formatting on some of the sheets. That is why I am looking for some other way to eliminate the style.

    If I copy the sheet, then the styles come with the copied sheets. And can't be deleted in the copied worksheet either.

    You aren't the first person to be stumped by this problem.

  9. #9
    New Member
    Join Date
    Apr 2011
    Location
    Bergenfield, NJ
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Style problem - unable to delete it

    Hi everyone. This is my first time posting on this forum. I actually found a solution to this issue and want to contribute.

    Here are the step-by-step process in removing all styles including the ones with invalid names:

    1. Rename your Excel workbook from .xlsx or .xlsm to .zip

    2. Open the workbook with WinZip and navigate to xl folder

    3. Right click on styles.xml and choose Open With Notepad

    4. Hit Ctrl F to search for cellstyles (with the open XML tag mark in the front, can't make it show in the post)

    5. Cancel out of the find dialogue box, move your cursor before cellstyles and hit enter twice (this is to mark where the beginning of this tag is)

    6. Hit Ctrl F again and search for /cellstyles>

    7. Highlight everything between step 5 and step 6 (everything in the cellstyles tags including the tags) and hit delete

    8. Save the Notepad and exit Notepad. WinZip should now ask you whether to update the file. Select Update Zip file with changes. Hit OK and close out WinZip.

    9. Rename the workbook zip file back to .xlsx or .xlsm

    The deed is done!

  10. #10
    New Member
    Join Date
    Apr 2011
    Location
    Bergenfield, NJ
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Style problem - unable to delete it

      
    Actually, I need to revise the solution I provided:

    HTML Code:
    1. Rename your Excel workbook from .xlsx or .xlsm to .zip
    
    2. Open the workbook with WinZip and navigate to xl folder
    
    3. Right click on styles.xml and choose Open With Notepad
    
    4. Hit Ctrl F to search for "<cellstyles"
    
    5. Replace everything between "<cellstyles" and "/cellstyles>" with the following XML code:
      <cellStyles count="1">
        <cellStyle name="Normal" xfId="0" builtinId="0" customBuiltin="1" />
      </cellStyles>
    
    6. Save the Notepad and exit Notepad. WinZip should now ask you whether to update the file. Select Update Zip file with changes. Hit OK and close out WinZip.
    
    7. Rename the workbook zip file back to .xlsx or .xlsm
    The reason that you need to keep this section is that if you try to save the .xlsm or .xlsx as .xlsb, and resave the .xlsb, it will give you an error.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com