Forum Discussion

pc321's avatar
pc321
Copper Contributor
Nov 10, 2023

collapsing (not sure if that's the correct term) individual cells/rows after expansion

So first off, I'm not sure if using the term "collapsing" is what I want to do. I've found information about grouping rows, but that doesn't seem to fit what I'm trying to accomplish.

 

I've created an updating spreadsheet where I've imported outlook e-mails into excel (data...get data...other sources...ms exchange). So far, so good. 

 

I then eliminated many of the columns, keeping only the ones I need.

 

One of the columns is "text body." As you might imagine, most e-mails will take up much more than the one line allocated in the corresponding cell. To view the full contents, I double click on the cell which expands the cell so I can read the contents. All of the other cells in that row automatically change to the same height as the "text body" cell that expanded.

 

So now to my issue...I can't figure out (an easy way) how to "collapse" (if that's the correct term) the corresponding row after it's expanded. 

 

I played around with "format cells" "alignment", toggling "wrapped text" on and then back off. That had the effect of collapsing the cells like I want, but this seems like a cumbersome way to do it. 

 

 

excel
Formulas and Functions
office 365

1 Reply

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 12, 2023

    pc321 

    When you double-click a cell to expand it and view the full contents, Excel automatically adjusts the row height to accommodate the expanded cell. However, Excel does not have a built-in feature to collapse the row back to its original size once expanded via a double-click. You are right in your observation, toggling "wrapped text" on and off is not the most efficient method for collapsing the expanded rows.

    Here is a workaround that might help manage this situation more efficiently:

    1. VBA (Visual Basic for Applications) Macro:

    You can use VBA to create a macro that collapses the row height back to its original size. This code would collapse the row to a predefined height when a specific cell is clicked or changed. For example, if you click on a cell in column A, the corresponding row's height will revert to a set size.

    Here is an example:

    Vba code (is untested):

     

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim originalRowHeight As Single
    originalRowHeight = 15 ' Change this to your desired original row height

    ' Check if the double-click occurred in a specific column, for instance, column A
    If Target.Column = 1 Then ' Change the column number as needed
        Target.EntireRow.RowHeight = originalRowHeight
    End If
End Sub

     

    To implement this:

    • Press Alt + F11 to open the VBA editor.
    • In the left pane, find your worksheet in the list of VBAProject objects. Double-click it to open the code window for that sheet.
    • Copy and paste the code provided into this window.
    • Modify the column number (in the code, its set to column A, represented by Target.Column = 1) to match the column where you want this action to take place.

    This code sets the row height to a specific value (here, originalRowHeight = 15), which you can adjust as needed to match the height you want the row to return to after collapsing. AI was used to support the text.

     

    My answers are voluntary and without guarantee!

     

    I hope that I have understood your concerns correctly and that this information will help you.

     

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources