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
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
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.
ReplyDeleten8fan.net
www.n8fan.net
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
ReplyDeleteWhether 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