Clear cell styles but retain formatting

[andykingstonuk]

Hi all,

I have a client's spreadsheet which uses lots of cell styles.

I need to import this spreadsheet into my own workbook, but don't want to import all the cell styles across.

I have tried running a few 'Style Kill' type macros on the client's spreadsheet prior to importing, but, in addition to removing the cell styles from the library, any cell that were using a cell style revert to normal (i.e. no formatting).

Is there a way to disconnect cell formatting from cell styles, so that I can then delete the cell styles without resetting all the cells?

Many thanks in advance,

 

[Chris Macro]

I have tried many cell style removes and the XLStylesTool is by far my favorite. You should definitely check it out!

 

[andykingstonuk]

Thanks for the tip but I have tried that add-in too... any cell formatted with a cell style which get deleted reverts to the default 'Normal' style

 

[Chris Macro]

Thanks for the tip but I have tried that add-in too... any cell formatted with a cell style which get deleted reverts to the default 'Normal' style

Have you tried this one? I believe it only removes unused cell styles

 

[Rick Rothstein]

I have a client's spreadsheet which uses lots of cell styles.

I need to import this spreadsheet into my own workbook, but don't want to import all the cell styles across.

I have tried running a few 'Style Kill' type macros on the client's spreadsheet prior to importing, but, in addition to removing the cell styles from the library, any cell that were using a cell style revert to normal (i.e. no formatting).

Is there a way to disconnect cell formatting from cell styles, so that I can then delete the cell styles without resetting all the cells?

Here I have used Selection, but you can change that to a specific range or ActiveSheet.UsedRange or whatever you need... does this do what you want?

Sub RemoveStyleKeepNumberFormat()
  Dim Cell As Range, Temp As Variant
  Application.ScreenUpdating = False
  For Each Cell In Selection
    Temp = Cell.NumberFormat
    Cell.ClearFormats
    Cell.NumberFormat = Temp
  Next
  Application.ScreenUpdating = True
End Sub

 

[andykingstonuk]

Rick,

Thanks, based on my limited knowledge of VBA, it looks like the code below will do what I need... temporarily save cell format, clear format, reapply format... assuming the reapply doesn't reapply cell style but just the formatting associated with the style?

I will try first thing tomorrow morning.

Here I have used Selection, but you can change that to a specific range or ActiveSheet.UsedRange or whatever you need... does this do what you want?

Sub RemoveStyleKeepNumberFormat()
  Dim Cell As Range, Temp As Variant
  Application.ScreenUpdating = False
  For Each Cell In Selection
    Temp = Cell.NumberFormat
    Cell.ClearFormats
    Cell.NumberFormat = Temp
  Next
  Application.ScreenUpdating = True
End Sub

 

[piecevcake]

2 simple solutions:
1. copy selection to Word and back (lose comments, names etc).
2. create a new style, !KEEPCellFormat. Untick ALL the boxes in the modify box. Apply it to your selection. Then delete it. The selection will be normal style, with its original formatting.