Remove cells wiith Strikethrough

Greemo

New Member
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
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
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
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
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
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top