Adjusting row heights on top of autofit

[louisli_evo]

Hi All,

I have a table that I need help on making a VBA/macro to adjust row heights on top of autofit-ting, autofit is good but it's too tight in viewing.

Mechanism:
I have a few columns of multi-row texts (B, D, and E), I need to:
- upon content change of that row, for example, I updated row 13 and by the time I hit enter or tab, it should start adjusting.
-- alternatively, I think I can run the VBA on demand, which I think it should be easier
- Range: row 2 to row 309
- if this row contains merged cells, skip to next row
- determine which column (B, D or E) on this row has the most lines
- add 25 pixels to the autofitted height


Could anyone shed some light on this issue? Thank you very much.

 

[jasonb75]

See if this code in the worksheet module does what you need,

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B309,D2:E309")) Is Nothing Then
    With Target
        .EntireRow.AutoFit
        .RowHeight = .RowHeight + 25
    End With
End If
End Sub

The code should autofit, then increase by 25 whenever you make a change to one of the specified cells. I haven't made any allowances for merged cells in the code.

 

[louisli_evo]

See if this code in the worksheet module does what you need,

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B309,D2:E309")) Is Nothing Then
    With Target
        .EntireRow.AutoFit
        .RowHeight = .RowHeight + 25
    End With
End If
End Sub

The code should autofit, then increase by 25 whenever you make a change to one of the specified cells. I haven't made any allowances for merged cells in the code.

 

[louisli_evo]

Hi Jason,

This is awesome.
I was stuck on how to determine which column has the most lines, without aware that an autofit can be performed in VBA.
Thank you very much.

 

[intedeco]

Is it possible to this for part of the row? I need to change the row height in colums k-z

 

[jasonb75]

You can't change the row height of selective columns, it's all or none.

 

[louisli_evo]

Is it possible to this for part of the row? I need to change the row height in colums k-z

I get what you mean.
Unfortunately this is not how Excel deals with row heights.
The rows are fixed so you can't change the height of some of the columns, but you can manually merge some cells to make it look higher than others.
If you can elaborate on the logic of your idea, it's easier for others to pick up.

 

[jasonb75]

but you can manually merge some cells to make it look higher than others.

Which might improve appearance but in many cases, cause bigger problems elsewhere (formulas, vba procedures, etc).

 

[intedeco]

Here is a screen shot:

 

[jasonb75]

Here is a screen shot:

I can see 3 replies to your posts so far (4 with this one) telling you that what you want is not possible. Adding a screen capture is not suddenly going to cause a miracle to happen.