Clear cell styles but retain formatting

andykingstonuk

New Member
Joined
Apr 13, 2014
Messages
4
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,
 

andykingstonuk

New Member
Joined
Apr 13, 2014
Messages
4
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,563
Office Version
2010
Platform
Windows
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?

Code:
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

New Member
Joined
Apr 13, 2014
Messages
4
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?

Code:
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
 

Forum statistics

Threads
1,085,157
Messages
5,382,049
Members
401,767
Latest member
JohnLeek

Some videos you may like

This Week's Hot Topics

Top