Excel Hiding and Unhiding Error Fix

Sometimes when you hide or unhide, rows or columns, you can get an error message saying that Excel can’t move objects off the sheet. The solution is in the macro below.

The problem usually relates to cell comments.

The macro below will amend all the shape objects on the current sheet (comments are a shape object) so that they move and re-size with the sheet. This will stop the error displaying.

Warning 1: If you have other shapes eg charts and text boxes this macro will affect them as well.
Warning 2: Macros cannot be undone – you may want to save your file before running the macro. If it doesn’t do what you expect, you can close it without saving.

Sub Fix_Comments()
'corrects an error that stops row hiding with sheets with cell comments
Dim s As Shape
On Error Resume Next
 For Each s In ActiveSheet.Shapes
      s.Placement = xlMoveAndSize
 Next
End Sub

This post shows you how to run a macro off an icon.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *