Auto adjust row height as data changes

[PuntingJawa]

I'm in search of a way to adjust the row height as data changes in column D for printing.
I know I can go to Home>Format>Autofit row height after the data has changed, but I would like this to be automatic as data changes.

 

[Joe4]

How exactly is the data changing?
Is someone manually entering things in cell to change their height, or are the changes due to formulas?

If it is manual edits that are changing the heights, you can use a "Worksheet_Change" event procedure code that runs automatically after someone has made an edit to a cell in column D.

Here is how to do that:
- Right-click on the sheet tab name at the bottom of the sheet
- Select "View Code"
- Paste the following VBA code in the window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if edit made to column D
    If Target.Column = 4 Then
'       Autofit row
        Rows(Target.Row).EntireRow.AutoFit
    End If
    
End Sub

Now, as you manually make edits to column D, it will cause this VBA code to fire on that row.

 

[PuntingJawa]

How exactly is the data changing?
Is someone manually entering things in cell to change their height, or are the changes due to formulas?

If it is manual edits that are changing the heights, you can use a "Worksheet_Change" event procedure code that runs automatically after someone has made an edit to a cell in column D.

Here is how to do that:
- Right-click on the sheet tab name at the bottom of the sheet
- Select "View Code"
- Paste the following VBA code in the window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if edit made to column D
    If Target.Column = 4 Then
'       Autofit row
        Rows(Target.Row).EntireRow.AutoFit
    End If
   
End Sub

Now, as you manually make edits to column D, it will cause this VBA code to fire on that row.

As usual I didn't think that far into an explanation figuring I was overlooking something .

Yes, data is being pulled by textjoin formula (Largest quantity 400 cells, the rest 150) which is changing by the =drop(filter( formula. I would post the array I have but it spans over 30 pages of information being pulled.

 

[Joe4]

Values that change due to formulas do not trigger Worksheet Change events to fire.
They would trigger a Worksheet Calculate event to fire, but the big drawback to Worksheet Calculate, is that unlike Worksheet Change, it CANNOT identify which cell's value changed, only that some cell somewhere on the sheet changed. So the best you can do with that is to loop through every row with something in it in column D and run VBA code on that. That could cause a performance hit, because that loop would run anytime any calculation is made anywhere on the sheet.

I am not sure if you want to go down that road or not...

 

[PuntingJawa]

Values that change due to formulas do not trigger Worksheet Change events to fire.
They would trigger a Worksheet Calculate event to fire, but the big drawback to Worksheet Calculate, is that unlike Worksheet Change, it CANNOT identify which cell's value changed, only that some cell somewhere on the sheet changed. So the best you can do with that is to loop through every row with something in it in column D and run VBA code on that. That could cause a performance hit, because that loop would run anytime any calculation is made anywhere on the sheet.

I am not sure if you want to go down that road or not...

I kind of figured something like that. Thanks for the time and another tool I can use elsewhere. I deal with packing slips and that macro will help in those other areas.

 

[PuntingJawa]

Actually, this will work for me! Since I use F2 to change through numbers, that is my trigger. You see, I use F2 to trigger the "=drop(filter(" formula. F is the only manual column where any changes are made. Thanks a bunch!

 

[Joe4]

You are welcome!
Glad to hear it works for you!