Avatar of Andreas Hermle
Andreas Hermle
Flag 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
Microsoft ExcelVBAMicrosoft Office

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shums Faruk

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
Subodh Tiwari (Neeraj)

@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.
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Subodh Tiwari (Neeraj)

Well it should work. I tested it with some dummy data and it worked.
BTW what's not working at your end?
Shums Faruk

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.
Andreas Hermle

ASKER
uppps, tried it on another workbook and it worked. Great :-)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Andreas Hermle

ASKER
Great job, thank you very much. Regards, Andreas
Subodh Tiwari (Neeraj)

You're welcome Andreas!