Keeping comments with text when filter removed (relative location for comment display)

If I insert comments into a filtered list (Excel 2010) and then remove the filter, each comment remains at its (absolute) location on the spreadsheet as a whole, rather than being close to the cell containing the comment.

For example, in the attached spreadsheet, I have added a comment to cell B121 while a filter is in place (Filtered!B121).  While filtered, B121 is on the 39th row of the displayed spreadsheet.

When I remove the filter (see copy in After_Filter_Removed!B121), the comment continues to be displayed near the 39th row of the displayed spreadsheet (i.e. near B39), with a line connecting it all the way down to cell B121.  

How can I get comments to be displayed in a "relative" rather than "absolute" location, i.e. so that comments are displayed near the cell being commented upon. when filters are added or removed?
sampledata.xlsx
LVL 6
Let's GoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tj aCommented:
You have 2 options.
1. You can either change the comment view to make comments only popup/visible when you select the cell with the comment. That way the popup will always be right beside the cell even if you delete some rows or columns.

2. Whenever you delete a row or column, you can manually move the comment box closer to the cell by dragging it to the desired position (to do this, hover your mouse over the edge of the comment until the cursor turns into cross-hairs then click and hold the comment box and drag it to wherever you want) then saving the file. When you reopen the file, the comment will be in the location you moved it to.

If you have a few comments, option 2 is better but if you have many comments maybe go with option 1 because it does what you want and keeps your spreadsheet looking less cluttered.
Roy CoxGroup Finance ManagerCommented:
I can't replicate the error when adding new comments to filtered rows.
Roy CoxGroup Finance ManagerCommented:
Just checked further and it is because the comment is visible when you remove the filter.

Note: takunda must have posted at about the same time as I did, so I have just seen his comments
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Tj aCommented:
Actually, you can also use some code to make the comment reset to the correct position. The details are here
http://contextures.com/xlcomments03.html#Reset

just scroll down to the part that says, "Reset Comments to Original Position"

Hope that helps.
Saqib Husain, SyedEngineerCommented:
I would suggest that in future whenever you insert a comment you should drag the comment box such that the top of the box is within the same row. This will get rid of the problem at least from filtering. You may, however, continued to be bothered if columns are hidden.
Let's GoAuthor Commented:
The macro at Takunda Jora's link is a useful workaround, but it needs to be run every time I change the filtering in my table.

Is there a way of triggering a macro each time a filter is applied or cleared?
Roy CoxGroup Finance ManagerCommented:
Why do you want the comment permanently on view?

You can use the WorkSheet Calculate event, but there must be a calculation to run, i.e. a formula in the sheet must re-calculate. I think ther simplest way would be to convert the data to a Table and add a Total RowManage data with Excel Tables

The add the code to the Calculate event
Option Explicit

Private Sub Worksheet_Calculate()

    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        cmt.Shape.Top = cmt.Parent.Top + 5
        cmt.Shape.Left = _
        cmt.Parent.Offset(0, 1).Left + 5
    Next
    MsgBox "Done"
End Sub

Open in new window


Here's a quick example using your workbook

Example File

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.