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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,971
Office Version
  1. 2016
Platform
  1. 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
36,971
Office Version
  1. 2016
Platform
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,515
Members
416,920
Latest member
Riskyplan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top