Deleting Rows Containing Struck-Through Text

Written by Allen Wyatt (last updated November 15, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


5

Tara often receives workbooks containing a single worksheet where some information in the worksheet contains text that is "struck through" (the text is formatted using strikethrough). Rows that contain struck-through text need to be deleted, but Tara isn't sure of how to quickly go about this.

If you only need to do this task once in a while, the following steps will help you out if it is easy to see which cells contain the strike-through formatting:

  1. Click at the left side of the first row you want to select—the first one containing struck-through text. The entire row should be selected.
  2. Hold down the Ctrl key as you click at the left side of any additional rows you want to delete. (You are building a selection set of rows to delete.)
  3. Press Ctrl+- (the minus key). The rows are deleted.

If it is not that easy to see all the rows containing struck-through text, you can rely on the Find feature to locate them for you:

  1. Press Ctrl+F. Excel displays the Find tab of the Find and Replace dialog box.
  2. Click the Options button to expand what is available in the dialog box.
  3. Make sure the Find What box is empty and place the insertion point within that box.
  4. Click the Format button. Excel displays the Find Format dialog box.
  5. Make sure the Font tab is visible. (It will probably be displayed by default.)
  6. Click the Strikethrough check box a time or two until you see it is selected. (You shouldn't need to change anything else in the dialog box.)
  7. Click OK to dismiss the Find Format dialog box.
  8. Click Find All. The Find and Replace dialog box expands to show which cells contain strikethrough formatting, and the first result (in the dialog box) is selected.
  9. Press Ctrl+A. This selects all the results in the dialog box.
  10. Click Close to dismiss the Find Format dialog box. All the cells containing strikethrough text should now be selected. (Don't click anywhere else in the worksheet or you'll inadvertently unselect the cells.)
  11. Press Ctrl+- (the minus key). Excel displays the Delete dialog box.
  12. Click the Entire Row radio button.
  13. Click OK. The rows are deleted.

There is a drawback to this approach—it won't find any cells containing mixed formatting. In other words, if only some of the text within the cell uses the strikethrough attribute, then that cell won't be found in step 8.

This deficiency can be overcome by using a macro. Plus, a macro is a good approach if you need to perform this task quite often. Here's one that will do the trick:

Sub DeleteSTRows()
    Dim c As Range
    Dim bCheck As Boolean
    Dim J As Integer
    Dim iRows As Integer

    iRows = Selection.Rows.Count
    If iRows > 2 Then
        For J = iRows To 1 Step -1
            bCheck = False
            For Each c In Selection.Rows(J).Cells
                bCheck = IsNull(c.Font.Strikethrough)
                If Not bCheck Then bCheck = c.Font.Strikethrough
                If bCheck Then Exit For
            Next c
            If bCheck Then Selection.Rows(J).EntireRow.Delete
        Next J
    End If
End Sub

To use the macro, simply select the cells you want to affect, and then run it. The macro steps through each row of the selection (from the last row to the first) and then checks each cell within those rows. If the cell contains struck-through text, then a flag (bCheck) is set and the entire row is deleted. (The macro won't work if your selection contains only 1 or 2 rows—that few of rows is easy enough to check and adjust manually.)

Note in the middle of the macro that the IsNull function is used. This is the part that detects if the cell contains mixed formatting. If only some of the characters in the cell use strike-through, then a null value is returned by the Strikethrough property. This is checked by IsNull and assigned to the bCheck variable. If bCheck is still False (meaning that there is no mixed use of strike-through in the cell), then the entire cell is checked to see if it has the requisite formatting.

It should be noted that if you don't want the macro to actually delete rows, but would prefer to just clear them, you can replace the .Delete method in the macro with the .Clear method.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5270) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Jumping to the Start of the Next Data Entry Row

Want a quick way to jump to the end of your data entry area in a worksheet? The macro in this tip makes quick work of the ...

Discover More

Speeding Up Document Display

Are your documents displaying too slowly? You can configure Word so that it is as quick as possible on displaying by ...

Discover More

Determining the Number of Pages in Your Document

If your macro needs to know how many pages are in your document, you can use the Information method to get the desired ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Creating Selections

Want a really easy way to create a selection of a group of cells? Discover how to use the Extend key to make this task ...

Discover More

Moving and Copying Cells

At the very heart of editing is the ability to move and copy cells in a worksheet. Understanding the differences between ...

Discover More

Dates Copied Incorrectly

Under the right circumstances, you may notice problems when copying dates from one workbook to another. This tip explains ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 7?

2018-02-18 12:04:54

Willy Vanhaelen

Oops, I just realised that I uploaded the wrong version of the marco. Because it deletes rows a reverse loop must be performed starting at the last row and going up to the first.
Here is the right one:
(see Figure 1 below)


Figure 1. 




2018-02-11 11:38:45

Willy Vanhaelen

Although the macro of this tip works correctly, it can be simplified. It tests twice for struck through text in each cell. By declaring the check variable as a variant istead of a boolean you can do it in one go.

I also don't see why to restrict the macro to a selection of minimum 3 rows. If your table has a lot of columns it can be tedious even in 2 rows to look for struck through text escpecially while partial strikethrough is also considered. Why not let the macro do the job?

Here is a screen shot of the macro because de site doesn't accept the text version.
(see Figure 1 below)


Figure 1. 




2018-02-11 08:25:06

pos

Strange!


2018-02-11 05:36:26

Willy Vanhaelen

What happened with the text of my comment?
It was first refused because it was suspected to be spam and then accepted but all the linefeeds were removed.


2018-02-10 11:44:52

Willy Vanhaelen

Although the macro of this tip works correctly, it can be simplified. It tests twice for struck through text in each cell. By declaring the check variable as a variant istead of a boolean you can do it in one go.I also don't see why to restrict the macro to a selection of minimum 3 rows. If your table has a lot of columns it can be tedious even in 2 rows to look for struck through text escpecially while partial strikethrough is also considered. Why not let the macro do the job?Sub DeleteSTRows()Dim Check As Variant, Cel As Range, Ro As RangeFor Each Ro In Selection.Rows For Each Cel In Ro Check = Cel.Font.Strikethrough If IsNull(Check) Or Check = True Then Ro.EntireRow.Delete Exit For End If Next CelNext RoEnd Sub


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.