How to Reposition Excel Comments Near Their Associated Cells
Question details
Users need a method to bring detached comments or notes back to their original associated cells in a large Excel worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Navigating and managing large worksheets containing thousands of rows and comments.
- Observed behavior
- Comments and notes detach from their associated cells and move thousands of rows away, expanding the scroll range and making the worksheet difficult to read and navigate.
Before running any VBA macro to reposition elements in your worksheet, ensure you save a backup copy of your file, as macro executions cannot typically be undone using the standard Undo button.
Use a VBA Macro to Reset Comment Positions
Running a VBA script is the most efficient and practical way to automate the repositioning of thousands of detached comments back to their parent cells.
When dealing with thousands of rows, manually dragging each comment back to its source cell is too time-consuming. A VBA macro can programmatically loop through every comment in your active worksheet and reset its spatial coordinates (Top and Left properties) to match its parent cell.
Press 'Alt + F11' on your keyboard to launch the Microsoft Visual Basic for Applications editor.
In the top menu, click 'Insert' and select 'Module' to create a blank script window.
Paste a standard comment-reset script (for example, a loop that sets 'cmt.Shape.Top = cmt.Parent.Top' and 'cmt.Shape.Left = cmt.Parent.Offset(0, 1).Left' for each comment).
Press 'F5' or click the 'Run' button. The macro will execute and instantly snap all stray comments back next to their respective cells.
Use the Find Tool for Immediate Navigation
If you do not want to use VBA and only need to read a few specific comments, using the built-in Find tool is a quick manual alternative.
Fix and Manage Stray Comments Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful built-in support for VBA and macros, allowing you to easily run scripts to reset floating comments. It effortlessly manages massive datasets with thousands of rows while maintaining perfect compatibility with your existing Excel workflows.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the worksheet containing the detached comments.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Macros', or simply press 'Alt + F11' to open the VBA Editor.
- 3. Insert and Run the Macro: Insert a new module, paste your comment-repositioning VBA script, and click Run to instantly restore all comments to their correct cell locations.
Frequently Asked Questions
Why do Excel comments move away from their original cells?
Comments can drift over time when rows or columns above and to the left of them are frequently inserted, deleted, hidden, or resized. In large files, this cumulative shifting causes the comment's shape to lose its original spatial relationship with the parent cell.
Can I lock comments so they don't move around in the future?
Yes. Right-click the cell containing the comment and select 'Edit Comment'. Then, right-click the border of the comment box, choose 'Format Comment', navigate to the 'Properties' tab, and select 'Move and size with cells'.
Is there a way to reset comment positions without using VBA?
Without VBA, you must manually drag each detached comment box back to its parent cell. Because this is highly impractical for worksheets with thousands of rows, using a VBA macro is the generally accepted and recommended solution.
Will running a repositioning macro delete the text inside my notes?
No. A repositioning macro only modifies the display coordinates (Top and Left properties) of the comment box. The actual text and data inside the comments remain completely unchanged.
