Written by Allen Wyatt (last updated November 15, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
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:
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Using the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of ...
Discover MoreWhen you click on a cell, you expect the cell to be selected. What happens, though, if you are instead taken to an ...
Discover MoreReferring to a range of cells on the current worksheet is easy. But what if you want to refer to a range on a number of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments