0

I have some cells whose content is determined by an INDEX formula. They are horizontally merged cells, on a single row.

The content they can show is quite variable in length, so the cells are set to wrap text. When they are updated by the change in the index reference value, it happens that they are too short to show all that they should show.

Thing is, I don't want to manually set a large height for the couple of instances where the content overflows, as it presents badly in all other cases ; and when they do overflow, there is no way other than manual to set an appropriate height.

That means that when I double click the line to auto-set height, it stays at default ; and if I actually manually enlarge it and then double click the same auto-set height line : it goes back to default and only part of the first line shows.

I there any way to automatically tell the sheet to auto-set the height of the rows where cells have been updated due to change of index value ?

Improve this question
4
  • I cannot confirm this as of Excel in Office 365 ProPlus (Ver. 1808). Double clicking the row adjusts the height fine in terms of enlarging or shrinking as needed. Just not automatically.
    – Wolfgang Jacques
    Commented May 15, 2019 at 13:31
  • I'm on ProPlus version 1904. @WolfgangJacques Just to be clear, regular auto resizing works fine ; whereas auto resizing dynamic content that gets filled from INDEX formula doesn't.
    – Saryk
    Commented May 15, 2019 at 13:37
  • Are any of the cells merged?
    – Greg Viers
    Commented May 15, 2019 at 14:32
  • @GregViers as a matter of fact yes, but only horizontally. I just checked, and it works fine on a single cell... I'll edit the question.
    – Saryk
    Commented May 15, 2019 at 14:42

1 Answer 1

Reset to default
1

This is a known issue.

You cannot use the AutoFit feature for rows or columns that contain merged cells in Excel.

You can fix it by unmerging your cells.

If you want them to autofit after a recalculation, add this subroutine to your sheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    For Each c In Target.Cells
        c.EntireRow.AutoFit

    Next c
End Sub
Improve this answer
8
  • Sure, autofit works if I unmerge my cells. The question I'm asking is, even on unmerged cells, how do I auto adjust when the cell gets updated from resolving an Index function ?
    – Saryk
    Commented May 15, 2019 at 17:04
  • I have edited my answer.
    – Greg Viers
    Commented May 15, 2019 at 17:11
  • Would it work to have the macro unmerge, do this autofit, then remerge?
    – fixer1234
    Commented May 15, 2019 at 17:25
  • 1
    @fixer1234 - probably, but the result would not be desirable, since the autofit would produce a height based on the unmerged width, which is less, and make the row too high.
    – Greg Viers
    Commented May 15, 2019 at 17:32
  • @GregViers Thanks for the edit ; unfortunately this does not work. Bear in mind I'm not selecting and changing the cell's content directly, the content that needs fitting is the result of a formula.
    – Saryk
    Commented May 16, 2019 at 7:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .