Fabulist

Board Regular
Joined
Jan 28, 2014
Messages
107
Hello,

Would it be possible to clear the formatting cells that do not contain anything, including text, and are also not filled with color?

There is a massive spreadsheet, that has had multiple rows inserted over the years, which rows have shared the same formatting (usually alignment, bold or not) of their preceding row, but are empty and can be restored to their default condition as with real unformatted cells (e.g. default formatting of a new sheet). All filled cells (color, pattern, etc.) are to remain as they are/

Is this possible with VBA or any other way? I cannot figure out if this is possible simply with find and replace.

Attached, a file where only formatting of cells with no values of any kind or fill should be cleared (e.g. A1 to B1 & A17 to B17 but not I15 to I17 etc.)
New Microsoft Excel Worksheet

Thank you in advance.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,677
Office Version
  1. 2016
Platform
  1. Windows
Try something like this

VBA Code:
Sub clearformatEmptycells()
'Change sheet name
Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats

End Sub
 

Fabulist

Board Regular
Joined
Jan 28, 2014
Messages
107
Thanks, but your code clears the formatting of cells that have a cell fill and either have text or are empty.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,909
Messages
5,544,994
Members
410,647
Latest member
LegenDSlayeR
Top