Pages

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

End With


End Sub

3 comments:

  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.

    n8fan.net

    www.n8fan.net

    ReplyDelete
  2. Do you use Excel? If so, when were you introduced to it? Do you use the PC version of Excel or the Apple version? Do you also use Macros (VBA) with Excel? Macros, made up of VBA is the backbone of Microsoft Excel. excel dashboard software

    ReplyDelete
  3. Whether we talk of business automation, or business accounting, excel has it all. Excel is perchance the most important computer software program used across diversified business sectors in the world. salesforce custom dashboard

    ReplyDelete