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
Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Sub RowHeightAutofit()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> "DataSource" Then
        ws.Rows(2).AutoFit
        ws.Rows(2).RowHeight = ws.Rows(2).RowHeight + 4
    End If
Next ws
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsExcel & VBA ExpertCommented:
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)Excel & VBA ExpertCommented:
@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.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Download eBook
Andreas HermleTeam leaderAuthor Commented:
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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Well it should work. I tested it with some dummy data and it worked.
BTW what's not working at your end?
ShumsExcel & VBA ExpertCommented:
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 HermleTeam leaderAuthor Commented:
uppps, tried it on another workbook and it worked. Great :-)
Andreas HermleTeam leaderAuthor Commented:
Great job, thank you very much. Regards, Andreas
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Andreas!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.