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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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
Back
Top