Can I sort or filter or delete by strike-through?

[Guest]

I've got a huge database in Excel, and many of the records have had text
formatted with a strike-trhough. Initially, this was so I could track what
had been 'deleted', but now I want to remove those records. Rather than go
through them individually, is there a quicker way?

 

[Dave Peterson]

What version of excel are you using?
Is this in a single column?
Is the formatting for the whole cell--or for just some characters in that cell?
Do you have any errors like #N/A or #value! in that range?

If you're using xl2002+
And if you don't have any errors in that column and the whole cell is formatted
as strike through, you can use this technique.

Select the column with the strikeouts.
Edit|replace
Click the Options button if you don't see the Formatting options
Click the Format button for the "Find What" box.
On the Font tab, click the striketrhough box
(and don't specify any other conditions)
Click ok.
Put this in the "replace with" box:
=na()
Replace all

All of the cells that are formatted as strikethrough will be changed to formulas
that return #N/A.

With that column still selected
Edit|goto|Special|Formulas
Uncheck Numbers, Text, Logicals. Keep Errors checked.
Click ok.
Edit|Delete|Entire row.

========
If you're aren't running xl2002+, or you have cells that are have some
characters formatted as strikethrough (and some not), or you have formulas that
return errors in that range, then this won't work.

========
An option is to use a macro that looks at the range and deletes the row based on
formatting.

But it would help if you could add a few details (what column/columns, whole
cell is formatted or not) to get code that would work the first time.

 

[JW]

Couple different ways to do this. You could use a function:
Function isStriked(cellTest As Range) As Boolean
Application.Volatile
If cellTest.Font.Strikethrough Then
isStriked = True
Else
isStriked = False
End If
End Function

Then, in a helper column, you could use something like
=isStriked(A2). It will return the word True if A2 is striked and
False if it isn't. Sort on the column, filter on the column,
whatever.

Or you could use a loop to cycle through a range of cells and delete
the whole row if the cell in the range is striked.
Sub deleteStrikes()
Dim rNum As Long
rNum = Cells(Rows.Count, 1).End(xlUp).Row
For i = rNum To 2 Step -1
If Cells(i, 1).Font.Strikethrough Then _
Cells(i, 1).EntireRow.Delete
Next i
End Sub

 

[JW]

After reading Dave's post, I feel I better clarify the code I posted
earlier. The function and the sub will only work if the whole cell is
formatted as strikethrough.

 

[Dave Peterson]

But it wouldn't be difficult to modify based on what the OP really wants.

As written, the cell has to be formatted as strikethrough. And that could be
what the OP really wants.

 

[Guest]

Hi Annie:

Do want you want to clear cells containing strikethroughs or delete the
cells or delete the entire row containing the cells?

 

[Guest]

I've struck through the entire row for each of the records that have been
'deleted'. So now I want to actually delete those rows.

 

[n1_andrew]

I have tried the code and everything worked fine. When I save and exit the worksheet and I open it back up I find that the function does not work anymore resulting in a #NAME? error. I dont understand why it does this and I really need some help. I can delete the excel document and open a new one and the same thing happens to me.