Results 1 to 6 of 6

Thread: Remove cells wiith Strikethrough
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2014
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Remove cells wiith Strikethrough

    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

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,315
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Remove cells wiith Strikethrough

    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("A1:Z99").Replace "*", "", SearchFormat:=True
      Application.FindFormat.Clear
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    May 2014
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove cells wiith Strikethrough

    Hi Rick,

    Works like a charm. Thank you!

    Greemo

  4. #4
    Board Regular
    Join Date
    Jan 2009
    Location
    Hong Kong
    Posts
    791
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove cells wiith Strikethrough

    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
    A simple spreadsheet you can manage could be a better choice than a sophisticated spreadsheet you need help from time to time.
    http://wmfexcel.com/welcome/

  5. #5
    New Member
    Join Date
    May 2014
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove cells wiith Strikethrough

    Quote Originally Posted by Rick Rothstein View Post
    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("A1:Z99").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

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,315
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Remove cells wiith Strikethrough

    Quote Originally Posted by Greemo View Post
    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
      On Error GoTo NoStrikethroughs
      Range("A1:Z99").Replace "*", "", SearchFormat:=True
    NoStrikethroughs:
      Application.FindFormat.Clear
    End Sub
    Code:
    Sub ClearStrikethroughCells()
      Application.FindFormat.Clear
      Application.FindFormat.Font.Strikethrough = True
      Application.DisplayAlerts = False
      Range("A1:Z99").Replace "*", "", SearchFormat:=True
      Application.DisplayAlerts = True
      Application.FindFormat.Clear
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •