• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

Excel filter by strike though

I have an Excel spreadsheet where certain rows have fields with the font has a strike though
Can I filter these in some way
Gordon
0
GiaHughes
Asked:
GiaHughes
  • 4
  • 3
2 Solutions
 
ProfessorJimJamCommented:
you can do this with using a helper cell and UDF


i have created the attached workbook as example.  i put the code below and then see column A has data with some cells  strike though and then i used column B with UDF and then i filtered column B .
Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

Open in new window

Book1.xlsm
0
 
Pallavi GodseSubject Matter ExpertCommented:
Please try the code below -
Sub StrikeOut()
Dim cll As Range
Dim rng As Range
Dim rC As Integer

rC = Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count 'Gets the last row to check
Set rng = Sheets("Sheet1").Range("A1:A" & rC) ' Sets the range to search through
For Each cll In rng
    If cll.Font.Strikethrough = True Or cll.Offset(0, 1).Font.Strikethrough = True Then 
        cll.Font.Strikethrough = True
        cll.Offset(0, 1).Font.Strikethrough = True
    End If
Next cll
End Sub

Open in new window

0
 
GiaHughesAuthor Commented:
Hi
Where do I go in Excel to add these codes
Gordon
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ProfessorJimJamCommented:
please download the attached i posted above

it already has the code in it.

if you press ALT+f11 you will see the code in a module.

for more detail on how to place the code on workbook see this link http://www.rondebruin.nl/win/code.htm
0
 
GiaHughesAuthor Commented:
OK have worked out how to input the code, have to select the developer option on the ribbin
Now, the column to use is B and not A, what do I change in the following

Sub StrikeOut()
Dim cll As Range
Dim rng As Range
Dim rC As Integer

rC = Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count 'Gets the last row to check
Set rng = Sheets("Sheet1").Range("A1:A" & rC) ' Sets the range to search through
For Each cll In rng
    If cll.Font.Strikethrough = True Or cll.Offset(0, 1).Font.Strikethrough = True Then
        cll.Font.Strikethrough = True
        cll.Offset(0, 1).Font.Strikethrough = True
    End If
Next cll
End Sub
0
 
ProfessorJimJamCommented:
Please see the attachment on my post , the code you are referring is not mine.
0
 
GiaHughesAuthor Commented:
I have read the posts and not sure how to action the codes, so I am sure both solution work, but I don't know how to deal with theses
So closing question
0
 
GiaHughesAuthor Commented:
Sorry guys
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now