Hiding rows with Threaded Comments

[Giordano Bruno]

I am using Excel 365 and have a worksheet with a threaded comment. I wish to hide all rows from above this comment to the bottom of the worksheet. If this were a normal comment, now called "Notes", I could format the comment to move and size with cells and this would allow me to hide the rows. I don't have this option with Threaded Comments. Does anyone have a workaround that will allow me to hide these rows without receiving a "Can't push objects off the sheet" message.
All replies will be acknowledged and most gratefully received.

 

[Fluff]

Not sure it's possible with the new comments. Cant you use Notes instead?

 

[Peter_SSs]

Are you trying to do this manually or by code?

I wish to hide all rows from above this comment to the bottom of the worksheet.

What exactly does this mean if there is a threaded comment in, say, C10 and data goes to row 20?

 

[Giordano Bruno]

Hi Fluff. Thanks for the confirmation. I have no control over the users. I try to make everything foolproof, but once again they've outwitted me. It's annoying that MS have released their Threaded Comments without a full suite of attributes. No doubt they know what they're doing. They're not fools. (Can't seem to find an emoticon for sarcastic smile with hint of a sneer).

Hi Peter. Yes, My standard data panel is at the top of the worksheet and the used panels are added below this. I want to alternately hide everything above row 34 or, if the standard data panel is being edited, hide everything from row 34 down to the bottom of the worksheet and this includes row 238 where the comment has been inserted. This looks neat an makes it impossible for the user to accidently format a cell in row 1048576 with disastrous results. It seems that I can no longer do this if threaded comments are being used.

 

[Peter_SSs]

I want to alternately hide everything above row 34 or, if the standard data panel is being edited, hide everything from row 34 down to the bottom of the worksheet

How are you doing that (assuming threaded comments don't exist in the sheet for the moment). I'm trying to replicate your situation.

 

[Giordano Bruno]

Hi Peter. Here's the bare bones of the code. "WksTop" is around row 34 and the threaded comment is down on row 238.

With shtXYZ
shtEnd = .Cells.Rows.Count
WksTop = .Range("TopWeeks").Row + 1
.Range(Cells(WksTop, 1), Cells(shtEnd, 1)).EntireRow.Hidden = True
End With

This runs after notes have been formatted to resize with cells, but I cannot do the same with Threaded Comments.

 

[Peter_SSs]

Does anyone have a workaround that will allow me to hide these rows without receiving a "Can't push objects off the sheet" message.

You could consider something like this - definitely not a 'neat workaround' but may suffice if you are stuck with threaded comments. BTW, this is not a strong area of mine, just done some experimenting so there may well be better ways.

Basically my approach is to
- Have another worksheet to store the comments while the rows are hidden
- Remove the comments
- Hide the rows

Then when you want to go back the other way
- Unhide the rows
- Copy the comments back

Codes might go something like this

Sub SaveCommentsAndHideRows()
  Dim shtXYZ As Worksheet, shtC As Worksheet
  Dim shtEnd As Long, WksTop As Long, i As Long
  Dim ct As CommentThreaded
  
  Set shtC = Sheets("Comments")
  shtC.Columns("A").Delete
  Set shtXYZ = Sheets("Data")
  
  With shtXYZ
    .Activate
    For Each ct In .CommentsThreaded
      i = i + 1
      ct.Parent.Name = "TComment" & i
      ct.Parent.Copy
      With shtC.Cells(i, 1)
        .PasteSpecial Paste:=xlPasteComments
        .Value = "TComment" & i
      End With
      ct.Delete
    Next ct
    Application.CutCopyMode = False
    shtEnd = .Cells.Rows.Count
    WksTop = .Range("TopWeeks").Row + 1
    .Range(Cells(WksTop, 1), Cells(shtEnd, 1)).EntireRow.Hidden = True
  End With
End Sub

Sub UnhideRowsAndRestoreComments()
  Dim shtXYZ As Worksheet, shtC As Worksheet
  Dim shtEnd As Long, WksTop As Long
  Dim ct As CommentThreaded
  Dim CommentCell As Range
  
  Set shtC = Sheets("Comments")
  Set shtXYZ = Sheets("Data")
  
  With shtXYZ
    .Activate
    shtEnd = .Cells.Rows.Count
    WksTop = .Range("TopWeeks").Row + 1
    .Range(Cells(WksTop, 1), Cells(shtEnd, 1)).EntireRow.Hidden = False
    For Each ct In shtC.CommentsThreaded
      Set CommentCell = .Range(ct.Parent.Value)
      If Not CommentCell Is Nothing Then
        ct.Parent.Copy
        CommentCell.PasteSpecial Paste:=xlPasteComments
        ActiveWorkbook.Names(ct.Parent.Value).Delete
      End If
    Next ct
    Application.CutCopyMode = False
  End With
End Sub

 

[Giordano Bruno]

That is so smart Peter. Way outside the box. Brilliant. Hopefully there won't be a whole army of these comments.
Many thanks for your help. Above and Beyond and entirely unexpected. Thy name liveth for ever more in my code.

 

[Peter_SSs]

You're very welcome and thanks for the very kind words.