Advanced Filter using text in Comment box?

Hi Aron,

Here's an easy way to do this. The following macro function enables you to check whether any comment in a row contains a particular string of text.

Function InComment(Cell As Range, SearchText As String) As Boolean

' Yields true if the SearchText is found in the text contained
' in the input Cell's comment. If no comment exists, returns
' FALSE.

Dim Comnt As Comment

InComment = False

For Each Comnt In ActiveSheet.Comments
If Comnt.Parent.Row = Cell.Row Then
If Comnt.Text Like "*" & SearchText & "*" Then
InComment = True
Exit Function
End If
End If
Next Comnt

End Function

You can use this to flag (filter) all rows that contain comments with the desired text using this function. Example:

=InComment(B4,"313")

will yield True if any comment in row 4 contains the string "313". The column part of the range reference of the first argument is ignored.

Just be aware that the function will normally only calculate when you enter it or when the contents of cell B4 (or whatever cell you reference) changes, and that adding a comment to a cell does not cause the sheet to re-calculate. You can force the function to recalculate by referencing the string argument to some other cell (i.e., put "313" in some cell on the worksheet, then reference that cell in the function's second argument). Then when you change that string all the InComment calls dependent on it will re-calculate.

I hope this helps.

Damon

InComment = False For Each Comnt In ActiveSheet.Comments If Comnt.Parent.Row = Cell.Row Then If Comnt.Text Like "*" & SearchText & "*" Then InComment = True Exit Function End If End If Next Comnt

Great!! Thanks so much!

After returning "True" to the InComment Function, how might I then copy and paste that row (or a part of it) to a new sheet?

Thank you so much for helping!!
Aron

Never mind - I got it!

I switched to a Sub and used this:

Sub find313()
Dim Comnt As Comment
Set SearchRange = Range("Sheet1!H1:AL300")
With SearchRange
For Each Comnt In ActiveSheet.Comments
If Comnt.Text Like "*313*" Then
Comnt.Parent.EntireRow.Copy
Sheets("Sheet2").Activate
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial (xlPasteAll)
Sheets("Sheet1").Select
End If
Next Comnt
End With
End Sub

Which works for now - A function would work better but I haven't figured that out yet. But I can - Thanks so much!