Dynamically change Height of multiple rows

[drag1c]

Hi,

I would like to, on every change of data in range("C7:P27"), change height of row per data to fit inside.

I am using array formula:

=IF(IFERROR(INDEX(Database!A:A,MATCH(ROWS($1:1)+$B$7-1,Database!$P:$P,0)),"")=0,"",IFERROR(INDEX(Database!A:A,MATCH(ROWS($1:1)+$B$7-1,Database!$P:$P,0)),""))

This formula grabs data from another sheet and with Form ScrollBar (non activeX) I can dynamically change values in cell.

I though of a way Worksheet_Change event, but I dont know how to write it in VBA.
Standard Wrap Text does not work in this case. -> It has to be updated on every change, because data goes up/down in same column per array formula.

Does anyone have idea how it could be done?

 

[aaewalsh]

You can use the Worksheet_Change event in VBA to automatically adjust the row height based on the data in the specified range. Here is an example of how you can implement this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("C7:P27")

If Intersect(Target, rng) Is Nothing Then Exit Sub

Application.EnableEvents = False
'Loop through all cells in the specified range
For Each cel In rng
'Check if the cell has wrapped text
If cel.WrapText Then
'Adjust the row height to fit the wrapped text
                cel.RowHeight = cel.Height
End If
 Next cel
Application.EnableEvents = True
End Sub

This code will check the range "C7:P27" for any changes, and if any change occurs it will loop through the cells in the range and check if the cell has wrapped text. If it does, it will adjust the row height to fit the wrapped text. The line Application.EnableEvents = False and Application.EnableEvents = True is added in order to prevent the change event to trigger itself again and again.

 

[drag1c]

Sir, thank you for answer.

This does not work on my sheet. Reason might be because I am using array and scrollbar for dynamical change of formulas in each cell in range C7:P27.

Formula does change in each cell whenever I press scrollbar from picture below.

=IF(IFERROR(INDEX(Database!B:B,MATCH(ROWS($1:1)+$B$7-1,Database!$P:$P,0)),"")=0,"",IFERROR(INDEX(Database!B:B,MATCH(ROWS($1:1)+$B$7-1,Database!$P:$P,0)),""))

I need something which will detect these changes. It is not like I write something in a cell -> It is automatically written.
Cell which triggers change is $B$7 (counter cell for scrollbar)

 

[drag1c]

I've solved it with this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static iRow As Long
    
    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("$C$7:$P$21")) Is Nothing Then
        If Target.Rows.Count > 1 Then Exit Sub
            If iRow > 0 Then
                Rows(iRow).RowHeight = 24.75
            End If
            iRow = Target.Row
            Target.EntireRow.AutoFit
            If Target.EntireRow.Height < 22 Then
                Rows(iRow).RowHeight = 24.75
            End If
    End If

Application.EnableEvents = True
End Sub

Seems better than to have everything adjusted.