any way to Filter by comments ?

[iffi]

hi everyone, there r so many comments in different excel cells. i want to filter that data by comments so that i can do my work easily instead of looking at each cell and finding out which cell has comment.
what could be the easy way for this ? plz kindly reply me.
Thanks for ur co-operation as usual

 

[jim may]

Post this into a Standard module
Highlight The range you wish to include Before Running Macro...
Caution Note before running -- See Next to last Code line Comment

Good Luck,
Jim

Sub CopyComments()
Dim s As String
Dim r As Range
Dim i As Long
i = 1
For Each r In Selection 'Range("B3:D7")
    On Error Resume Next
    s = r.Comment.Text
    On Error GoTo 0
    If s <> "" Then
    Cells(i, 13).Value = WorksheetFunction.Substitute(s, Chr(10), " ")
    i = i + 1
    End If
    s = vbNullString
Next r
ActiveSheet.Range("M:M").ColumnWidth = 36  'THIS SHOULD BE A BLANK COLUMN !!!
End Sub

 

[tweedle]

not really filtering; but utillites

Sub CommentsShowAll()
'Display all comments of the active sheet
Dim C As Comment
For Each C In ActiveSheet.Comments
    C.Visible = True
Next
End Sub
Sub CommentsShowMine()
'Display all current user comments of the active sheet
Dim C As Comment
For Each C In ActiveSheet.Comments
    If C.Author = Application.UserName Then
        C.Visible = True
    Else
        C.Visible = False
    End If
Next
End Sub
Sub CommentsHideAll()
'Hide all comments of the active sheet
Dim C As Comment
For Each C In ActiveSheet.Comments
    C.Visible = False
Next
End Sub
Sub CommentsShowIf()
'Display all comments of the active sheet if search string is found in the comment
Dim C As Comment
searchstr = LCase(InputBox("Show Comments with:"))
For Each C In ActiveSheet.Comments
    If InStr(1, LCase(C.Text), searchstr) > 0 Then
        C.Visible = True
    Else
        C.Visible = False
    End If
Next
End Sub

 

[iffi]

Thanks for ur quick reply, this one is nice but i also got a user defined function formulae for this which is working great, by this below function, i can filter by True value and return the cells only which has comment

Function CellHasComment(c As Range)
     Application.Volatile True
     CellHasComment = Not c.Comment Is Nothing
End Function

Now you can use a formula such as the following within a worksheet:
=CellHasComment(B2)

When the formula is executed, it returns either True or False, depending on whether cell B2 has a comment or not. You can then use Excel's filtering capabilities to display only those rows that have a True returned by the formula.