Filtering cells with comments

[LeighC]

Hi there! Hope someone can help me with my problem. I have a large spreadsheet with over 3,000 rows of data and I have marked each with a specific action required in column D. However, I also attached some comments to the cells in the actions column and I would now like to filter out these cells so I can follow them up. I have tried Autofilter and Advanced Filter and read through most of the Help guide but can't find anything to help me do this. Can you filter to show only those cells in a column with a comment attached? Any help would be greatly appreciated!

 

[max8719]

This function will return that count for you...

Function CommentCount(Rng As Range) As Long
  Dim Cell As Range
  For Each Cell In Rng
    If TypeName(Cell.Comment) = "Comment" Then CommentCount = CommentCount + 1
  Next
End Function

But note that adding or deleting a comment does not trigger any events, so this function will not automatically updates its count... you will have to press F9 after adding or deleting a comment, or wait for some other action that triggers a recalculation of the sheet, to force the function to update its count.

Thank you for a fast and knowledgeable response Rick. This function works a treat.

My next obstacle is password protecting multiple sheets within a work book. I can only find options to protect individual sheets or the whole work book. Is there a way around this?

 

[sadams1]

The code below will place a 'True' in the next column to any cell containing comments on the activesheet, you could then use Autofilter on that column to only show those rows which contain comments. Note that this will overwrite cells you may need, if you have comments in any other cell other than those in column D. Please repost if this is a problem and we'll see if there's a solution:-


Public Sub FindComments()

Dim c As Comment



Application.ScreenUpdating = False

For Each c In ActiveSheet.Comments

c.Parent.Offset(0, 1).Value = "True"

Next c

Application.ScreenUpdating = True



End Sub


*******************************************************************

I realize this is a really old thread but is it possible to have this limited to one column instead of the entire sheet?

 

[Eric W]

Try:

Public Sub FindComments()
Dim c As Comment

Application.ScreenUpdating = False

For Each c In ActiveSheet.Comments

    If Not Intersect(c.Parent, Range("A:A")) Is Nothing Then
        c.Parent.Offset(0, 1).Value = "True"
    End If

Next c

Application.ScreenUpdating = True

End Sub

Put your column where I marked in red.

 

[sadams1]

hello Eric! Thank you very much for this.

 

[Eric W]

You're welcome.

 

[Rick Rothstein]

Try:

Public Sub FindComments()
Dim c As Comment

Application.ScreenUpdating = False

For Each c In ActiveSheet.Comments

    If Not Intersect(c.Parent, Range("A:A")) Is Nothing Then
        c.Parent.Offset(0, 1).Value = "True"
    End If

Next c

Application.ScreenUpdating = True

End Sub

Put your column where I marked in red.

If I understand the request correctly, this non-looping macro should also work...

Public Sub FindComments()
  On Error GoTo NoComments
  Range("[B][COLOR="#FF0000"]A:A[/COLOR][/B]").SpecialCells(xlComments).Offset(, 1).Value = "TRUE"
NoComments:
End Sub

Put your column (or two dimensional range) where I marked in red.

 

[sadams1]

Sorry to be a pain in the *** but how much of a stretch would it be to tweak this to search for cells highlighted in yellow instead of having comments?

 

[Rick Rothstein]

Sorry to be a pain in the *** but how much of a stretch would it be to tweak this to search for cells highlighted in yellow instead of having comments?

How did the cells get their color... manually, programmatically or conditional formatting?

Will there be any other colors in the range or only yellow?

What did you want done... put TRUE next to them?

A single column only or multiple (adjacent) columns?

 

[sadams1]

hello Rick! I was hoping Eric's code could be modified easily to do the same thing but to search for a manually highlighted cell in yellow instead of searching for comments. This would be in one column & put true in the column next to it.

 

[Rick Rothstein]

hello Rick! I was hoping Eric's code could be modified easily to do the same thing but to search for a manually highlighted cell in yellow instead of searching for comments. This would be in one column & put true in the column next to it.

Here is something close to the construction that Eric used which will do what you want (change the "A" to the column letter designation you want to process) ...

Public Sub FindYellowCells()
  Dim Cell As Range
  
  Application.ScreenUpdating = False
  
  For Each Cell In Columns("[B][COLOR="#FF0000"]A[/COLOR][/B]")
  
      If Cell.Interior.Color = vbYellow Then Cell.Offset(, 1).Value = "TRUE"
  
  Next
  
  Application.ScreenUpdating = True

End Sub

By the way, did you see the code I posted in response to your original question (Message #16 )?