Excel Filtering cells with comments :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Filtering cells with comments
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

LeighC
Welcome to the Board


Joined: 07 Jan 2003
Posts: 3
Location: Edinburgh, Scotland

Status: Offline

 Reply with quote  

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!

Post Tue Jan 07, 2003 12:20 pm 
 View user's profile Send private message

Mudface
MrExcel MVP


Joined: 19 Feb 2002
Posts: 3112
Location: Sunny, spring-like Hull
Flag: England

Status: Offline

 Reply with quote  

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

Post Tue Jan 07, 2003 12:53 pm 
 View user's profile Send private message

SimonP
Board Regular


Joined: 26 Sep 2002
Posts: 48


Status: Offline

 Reply with quote  

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

Post Tue Jan 07, 2003 12:57 pm 
 View user's profile Send private message Send e-mail

Mudface
MrExcel MVP


Joined: 19 Feb 2002
Posts: 3112
Location: Sunny, spring-like Hull
Flag: England

Status: Offline

 Reply with quote  


quote:

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

Post Tue Jan 07, 2003 1:05 pm 
 View user's profile Send private message

LeighC
Welcome to the Board


Joined: 07 Jan 2003
Posts: 3
Location: Edinburgh, Scotland

Status: Offline

 Reply with quote  

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?!

Post Tue Jan 07, 2003 1:40 pm 
 View user's profile Send private message

SimonP
Board Regular


Joined: 26 Sep 2002
Posts: 48


Status: Offline

 Reply with quote  

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

Post Tue Jan 07, 2003 3:39 pm 
 View user's profile Send private message Send e-mail

LeighC
Welcome to the Board


Joined: 07 Jan 2003
Posts: 3
Location: Edinburgh, Scotland

Status: Offline

 Reply with quote  

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

Post Tue Jan 07, 2003 3:53 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.