Automatically resizing rows when value changed

Wouldn't this work?

 Selection.Rows.AutoFit

Maybe

 .EntireRow.AutoFit

Or are there any merged cells in the rows? Merged cells create problems. Use Center Across Selection instead.

Hi, john_london,

if you monitor a cell which has a formula inserted you would need to apply the Calculate-event as that would be triggered on any calculation/change of formula. Workshet_change would need to monitor both cells which might be changed.

You might try to alter the code like

'...
    With AutoFitRng
      .MergeCells = False
      Range(str01).Formula = "=IF(ISERROR(VLOOKUP(Data!$A$39, all_data, MATCH(selected_date, Data!$1:$1, 0), FALSE)), ""n/a"", (VLOOKUP(Data!$A$39, all_data, MATCH(selected_date, Data!$1:$1, 0), FALSE)))"
      Range(str01).Calculate
      CWidth = .Cells(1).ColumnWidth
      MergeWidth = 0
'...

Ciao,
Holger

Hi Holger - thanks so much for replying. This is the code I used, but the row height stays the same? It seems it only updates when I have the calculate code you mention separately in the WorkSheet_Activate sub?

Private Sub Worksheet_Change(ByVal Target As Range)
  
Dim MergeWidth As Single
Dim cM As Range
Dim AutoFitRng As Range
Dim CWidth As Double
Dim NewRowHt As Double
Dim str01 As String
str01 = "CommentBox"

Worksheets("Summary").Unprotect

  If Not Intersect(Target, Range(str01)) Is Nothing Then
    Application.ScreenUpdating = False
    On Error Resume Next
    
    Set AutoFitRng = Range(Range(str01).MergeArea.Address)

    With AutoFitRng
      .MergeCells = False
      Range(str01).Formula = "=IF(ISERROR(VLOOKUP(Data!$A$39, all_data, MATCH(selected_date, Data!$1:$1, 0), FALSE)), ""n/a"", (VLOOKUP(Data!$A$39, all_data, MATCH(selected_date, Data!$1:$1, 0), FALSE)))"
      Range(str01).Calculate
      CWidth = .Cells(1).ColumnWidth
      MergeWidth = 0
      For Each cM In AutoFitRng
          cM.WrapText = True
          MergeWidth = cM.ColumnWidth + MergeWidth
      Next
      'small adjustment to temporary width
      MergeWidth = MergeWidth + AutoFitRng.Cells.Count * 0.66
      .Cells(1).ColumnWidth = MergeWidth
      .EntireRow.AutoFit
      NewRowHt = .RowHeight
      .Cells(1).ColumnWidth = CWidth
      .MergeCells = True
      .RowHeight = NewRowHt
    End With
    Application.ScreenUpdating = True
    
  End If
    
 Worksheets("Summary").Protect

End Sub

So, when I have the original code that I posted it works (the row height adjusts) when I select a different sheet and then come back to the original worksheet. I would hope that the user can just update the cell and the row updates rather than having to switch sheets.

Private Sub Worksheet_Activate()
  
 Worksheets("Summary").Unprotect
    
    Range("CommentBox").Formula = "=IF(ISERROR(VLOOKUP(Data!$A$39, all_data, MATCH(selected_date, Data!$1:$1, 0), FALSE)), ""n/a"", (VLOOKUP(Data!$A$39, all_data, MATCH(selected_date, Data!$1:$1, 0), FALSE)))"
    Range("CommentBox").Calculate
    
 Worksheets("Summary").Protect

End Sub

Hi, john_london,

change

  If Not Intersect(Target, Range(str01)) Is Nothing Then

which will not be triggered as the cell monitored is changed by formula not by entry/edit/query to

  If Not Intersect(Target, Range("J3:K3")) Is Nothing Then

which will monitor the cells changing to start the event.

Ciao,
Holger

Hi, John,

if your problem has been resolved please mark this thread as Solved via Thread Tools over the first post in this thread.

Ciao,
Holger