VBA - Hiding rows in worksheets with comments

[EirikDaude]

I have a workbook with several sheets in each. In every one of those sheets I only want to show rows 33:106. I tried just highlighting every sheet and hiding the relevant rows, but I got an error message "Cannot shift objects off sheet". After a bit of googling, as well as some trying and failing, I discovered that this is because there are comments in the worksheets, and when I e.g. hide rows 1:32 they may get repositioned so that they overlap with the other area I want to hide, or in the example below, they are repositioned to somewhere "outside" the worksheet.

Browsing the locals and object browser, it seemed that it was the topleftcell and bottomrightcell which gave the position of the comment, but trying to reposition the textbox using those addresses failed.

At the moment I have two solutions in mind:

1. Find a way to reposition the comment box before hiding the rows
2. Find a way to discover the address of the cell the textbox is linked to, so I can copy, remove, and then add a new comment with similar fields to the cell after the rest of the macro has run its course.

However, I have no idea how to do this, and my google-fu has failed miserably. Therefore, I decided to turn to your insight - I really hope you can help!

Sub hiderows()
  Dim i As Long, r As Range, c As Range, sh As Shape, ws As Worksheet
  
  For Each ws in Worksheets
    With ws
      For Each sh In ws.Shapes
        If sh.Type = msoComment Then
          Set sh.TopLeftCell = sh.TopLeftCell.Offset(-32, 0)
          Set sh.BottomRightCell = sh.BottomRightCell.Offset(-32, 0)
        EndIf
      Next
      ws.Rows("1:32").EntireRow.Hidden = True
    End With
  Next

End Sub

[Teeroy]

It sounds like you have comment and indicator displayed. If you change to indicator only displayed there shouldn't be a problem. You could probably even wrap your code in:

Application.DisplayCommentIndicator = xlCommentIndicatorOnly
... Your code ...
Application.DisplayCommentIndicator = xlCommentAndIndicator

[EirikDaude]

Thanks for your suggestion. Unfortunately, I still get an error when I run the macro:

Going back to the worksheet and attempting to hide the rows "by hand" after setting DisplayCommentIndicator to xlCommentIndicator only, I still get the error message:

[snb]

Why don't you post a sample workbook representing yours without sensitive content ?