Categories
Microsoft Office Technology

Filter Cells In Excel With Strike Through Formatting

I have just finished reviewing an excel spreadsheet where a number of the rows were formatted with strikethough : like this.

The formatting was valid and the rows have to stay there – but I have no need to review those rows. In excel you can filter rows, by contents or even by colour but not by format.

Enter a VBA user function:

Create a code module and paste in the following:

Function StruckThrough(R As Range) As Integer
    If R.Count = 1 Then StruckThrough = R.Font.Strikethrough
End Function

https://github.com/RossGoodman/Excel/blob/master/StruckThrough.bas

You can then add a new column for example A:A and enter the formula

=StruckThrough(B1)

and then copy this down. Column A will then flag true or false if the cell to the right is struck through and you can now filter by this new column.

Leave a Reply

Your email address will not be published. Required fields are marked *