Filtering cells with comments

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Filtering cells with comments

  1. #1
    New Member
    Join Date
    Jan 2003
    Location
    Edinburgh, Scotland
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Sep 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A slightly different take would be to create a custom function to return the actual comments as text into worksheet cells - then filter based on this 'new' column:

    Function Comm(ref As Range) As String
    On Error GoTo NoComment
    Comm = ref.Comment.Text
    Exit Function
    NoComment:
    Comm = ""
    End Function

    Simon

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2003-01-07 07:57, SimonP wrote:
    A slightly different take would be to create a custom function to return the actual comments as text into worksheet cells - then filter based on this 'new' column:

    Function Comm(ref As Range) As String
    On Error GoTo NoComment
    Comm = ref.Comment.Text
    Exit Function
    NoComment:
    Comm = ""
    End Function

    Simon
    Mindreader! I was going to post something similar if the OP had problems .

  5. #5
    New Member
    Join Date
    Jan 2003
    Location
    Edinburgh, Scotland
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks guys. The second option of pasting the actual comments into the cells is definitely preferable. Stupid question though - how do I create a custom function?!

  6. #6
    New Member
    Join Date
    Sep 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Press Alt+F11 to enter the VB editor.
    In the left pane select your workbook, then select Insert>Module.
    Copy & paste the code from my previous post into the right hand pane.
    Close the VB editor.

    Now in Excel, you can paste the new function as you would any built in function - it will appear in the User Defined categoy.
    Or just type it in i.e.

    =Comm(b5)

    Hope this makes sense
    Simon

  7. #7
    New Member
    Join Date
    Jan 2003
    Location
    Edinburgh, Scotland
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Simon - that's perfect! My colleague spent several days last week doing something similar so we're both v. grateful

  8. #8
    Board Regular
    Join Date
    Jan 2015
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtering cells with comments

    Quote Originally Posted by LeighC View Post
    Thanks Simon - that's perfect! My colleague spent several days last week doing something similar so we're both v. grateful
    Hi I have used this function but when I add or ammed a comment in the cell it doesn't automatically update the text value.
    For example. Q27 has an inserted comment Max Rochelle: RTW. On a different work sheet C13 has this function =Comm('2015'!Q27) However when I amend the comment in Q27 to RTW it doesn't change the text

  9. #9
    Board Regular
    Join Date
    Jan 2015
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtering cells with comments

    Basically all I need to do is count comments in a specified range

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,841
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Filtering cells with comments

      
    Quote Originally Posted by max8719 View Post
    Basically all I need to do is count comments in a specified range
    This function will return that count for you...

    Code:
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com