Hiding rows with Threaded Comments

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,219
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Not sure it's possible with the new comments. Cant you use Notes instead?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,323
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Jan 7, 2007
Messages
1,219
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,323
Office Version
365
Platform
Windows
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.
 
Last edited:

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,219
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,323
Office Version
365
Platform
Windows
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

Rich (BB code):
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

Rich (BB code):
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
 
Last edited:

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,219
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,323
Office Version
365
Platform
Windows
You're very welcome and thanks for the very kind words. :)
 

Forum statistics

Threads
1,082,607
Messages
5,366,587
Members
400,904
Latest member
ndaines meriabi

Some videos you may like

This Week's Hot Topics

Top