Dynamically change Height of multiple rows

drag1c

Board Regular
Joined
Aug 7, 2019
Messages
97
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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:
Code:
=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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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:

VBA Code:
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.
 
Upvote 0
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.
Code:
=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)),""))
1673851858245.png


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)
1673851950878.png
 
Upvote 0
I've solved it with this code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top