Remove cells wiith Strikethrough

Greemo

New Member
Joined
May 22, 2014
Messages
42
Hello,

I'm looking to create a procedure to look at a specific range of cells, then remove the cell contents for those that contain strikethrough formatting. How do I go about that? Can this be done without looping?

Thanks,

Greemo
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,419
Office Version
2010
Platform
Windows
This should do it (change the highlighted range to the actual range you want to process)...
Code:
Sub ClearStrikethroughCells()
  Application.FindFormat.Clear
  Application.FindFormat.Font.Strikethrough = True
  Range("[COLOR=#FF0000][B]A1:Z99[/B][/COLOR]").Replace "*", "", SearchFormat:=True
  Application.FindFormat.Clear
End Sub
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
A non-vba approach for your consideration.

1) Select the Range
2) Go to Find and Replace (CTRL H)
3) Click Options
4) Find what: Leave blank --> Format Dropdown --> Format... --> Font Tab --> Check Striketrhough --> OK
'Don't check any other format than Strikethrough unless you are intended to...
5) Find All 'note all the cells with Strikethrough should appear
6) Replace All with nothing

For more details, you may take a look at:
Quickly delete/hide records (rows) with Strikethrough format by using Find and a couple of simple techniques. | wmfexcel
 

Greemo

New Member
Joined
May 22, 2014
Messages
42
This should do it (change the highlighted range to the actual range you want to process)...
Code:
Sub ClearStrikethroughCells()
  Application.FindFormat.Clear
  Application.FindFormat.Font.Strikethrough = True
  Range("[COLOR=#FF0000][B]A1:Z99[/B][/COLOR]").Replace "*", "", SearchFormat:=True
  Application.FindFormat.Clear
End Sub
Rick,

I get a "We couldn't find anything to replace" error in a pop up window when there are no available cells with strikethrough formatting. Is there a way to not have an error presented when there are no strikethough formattings?

Thanks,

Greemo
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,419
Office Version
2010
Platform
Windows
Rick,

I get a "We couldn't find anything to replace" error in a pop up window when there are no available cells with strikethrough formatting. Is there a way to not have an error presented when there are no strikethough formattings?
What version of Excel are you using? I ask because I have XL2003, XL2007 and XL2010 and my code does not raise that error when there are no strikethroughs for any of these versions. I do not have XL2013 so I cannot test it there. Anyway, because I cannot repeat the error you are seeing, I can only guess at a way to stop it. I would think one of the two following macros should keep the error from appearing... use whichever one works for you.

Code:
Sub ClearStrikethroughCells()
  Application.FindFormat.Clear
  Application.FindFormat.Font.Strikethrough = True
  [B][COLOR=#FF0000]On Error GoTo NoStrikethroughs[/COLOR][/B]
  Range("A1:Z99").Replace "*", "", SearchFormat:=True
[COLOR=#FF0000][B]NoStrikethroughs:[/B][/COLOR]
  Application.FindFormat.Clear
End Sub
Code:
Sub ClearStrikethroughCells()
  Application.FindFormat.Clear
  Application.FindFormat.Font.Strikethrough = True
  [COLOR=#FF0000][B]Application.DisplayAlerts = False[/B][/COLOR]
  Range("A1:Z99").Replace "*", "", SearchFormat:=True
  [COLOR=#FF0000][B]Application.DisplayAlerts = True[/B][/COLOR]
  Application.FindFormat.Clear
End Sub
 

Forum statistics

Threads
1,078,134
Messages
5,338,430
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top