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. 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


Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...