Filter rows on comment text

Macro to hide and unhide the rows on comment text


Option Compare Text

Sub filteroncomments()

Dim commenttext As String
Dim commentrng As Range
Dim cl As Range

With ActiveSheet
    .FilterMode = False
    .UsedRange.EntireRow.Hidden = False
    On Error Resume Next
    Set commentrng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
    On Error GoTo 0
    If commentrng Is Nothing Then
        MsgBox "No comments on worksheet"
        Exit Sub
    End If
   
   
    Application.Calculation = xlCalculationManual
   
    .UsedRange.EntireRow.Hidden = True
   
    commenttext = InputBox("Enter comment to search")
   
    For Each cl In commentrng
        If cl.EntireRow.Hidden = True Then
            If InStr(cl.Comment.Text, commenttext) > 0 Then cl.EntireRow.Hidden = False
        End If
       
    Next
   
    Application.Calculation = xlCalculationAutomatic

End With


End Sub