Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

Set the row height to autofit in all workshets and increase that height by 4 points (by means of VBA)

Dear Experts:

For the currently active workbook I would like to run a macro that performs the following actions ...
... on all worksheets with the exception of a sheet named 'DataSource'

Set the row height of the second row of all the worksheets to autofit and increase the resulting row height (could be different in every worksheet) by 4 points.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As far as I understood, you want only row 2 to be autofit and after that all the rows height must be increase by 4. If that's the case you may try below:
Sub AutoAdjustRowHeight()
Dim Ws As Worksheet
Dim LR As Long
Dim Rng As Range
For Each Ws In Worksheets
    If Ws.Name <> "DataSource" Then
    LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
    Set Rng = Ws.Range("A3:A" & LR)
        Ws.Rows(2).AutoFit
        Rng.Rows.RowHeight = Rng.Rows.RowHeight + 4
    End If
Next Ws
End Sub

Open in new window

Change the column A to actual column which has last row, accordingly adjust Rng
@Shums
I think you misunderstood the requirement. :)
The requirement was like this...
Set the row height of the second row of all the worksheets to autofit and increase the resulting row height (could be different in every worksheet) by 4 points.
Avatar of Andreas Hermle

ASKER

Dear both,

thank you very much for your professional help. Subodh was right in his assessment of my requirements.

Subodh: Although your code looks logical and good to me, it regrettably does not work. Any idea why?

Regards, Andreas
Well it should work. I tested it with some dummy data and it worked.
BTW what's not working at your end?
If that's the case then Neeraj's code is working perfectly with me as well. Unless you have Wrap Text in second row of any sheet.
uppps, tried it on another workbook and it worked. Great :-)
Great job, thank you very much. Regards, Andreas
You're welcome Andreas!