Saturday, September 26, 2015

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
    Application.Calculation = xlCalculationAutomatic

End With

End Sub

1 comment:

  1. The mind is a great philosopher. And life is not a philosophy, life is a reality. And philosophy is an escape from reality; philosophy means thinking. Life is, there is no question of thought. You can simply jump into it. You can simply experience it. Visit my site for more information.Thank you.