Filtering cells with comments

LeighC

New Member
Joined
Jan 6, 2003
Messages
3
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!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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:-
<pre>
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
</pre>
 
Upvote 0
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
 
Upvote 0
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 :biggrin:.
 
Upvote 0
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?!
 
Upvote 0
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
 
Upvote 0
Thanks Simon - that's perfect! My colleague spent several days last week doing something similar so we're both v. grateful
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top