Notes "crushed" by Autofilter--How to prevent?

NigelTufnel

Board Regular
Joined
Apr 3, 2008
Messages
53
Office Version
  1. 365
Platform
  1. Windows
I frequently use autofilter on tables/lists containing cells with attached notes. Excel has this *maddening* behavior of changing the size of the attached note based on how many rows have been filtered out.

I have attached (in pictures) a sample small table I created with names and ages. I added a note that is visible (Pic #1). Then I filtered on ages between 30 and 35. You will see how the note gets "squashed" so now only part of the text is visible (Pic #2). If I want to read the entire note contents while the table is filtered, I have to resize the note. If I resize it, so the full text is visible (extending the box across hidden rows), then when I remove the existing filter, the note box grows much larger (Pic #3).

When I look at the format properties for the note in question, the property "Don't move or size with cells" is selected. I would guess that this means that turning on autofilter and filtering the list would not alter the size or location of the note....but this is not the case. I can't seem to find any other properties that give me the location and sizing behavior I want.

This is just a simple example, In reality, when using tables with thousands of rows, the notes will get crushed to be height zero (and then keep that height when unfiltered!), or they will get moved half-way down the table, etc.

I realize the reason for this has to do with the rows that get hidden and un-hidden, but what setting can I use to keep the size of the note box constant and the position relative to the host cell constant regardless of the level of filtering?

Any suggestions are appreciated.
 

Attachments

  • Pic #1--Original Note.png
    Pic #1--Original Note.png
    13.1 KB · Views: 7
  • Pic #2--Note after filtering list.png
    Pic #2--Note after filtering list.png
    16.4 KB · Views: 9
  • Pic #3--After re-sizing to original size while filtered...then unfiltered.png
    Pic #3--After re-sizing to original size while filtered...then unfiltered.png
    19.3 KB · Views: 10

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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