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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,692
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,119,094
Messages
5,576,072
Members
412,696
Latest member
khanhvy31
Top