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

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
@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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Andreas HermleTeam leader

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Well it should work. I tested it with some dummy data and it worked.
BTW what's not working at your end?
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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 leader

Author

Commented:
uppps, tried it on another workbook and it worked. Great :-)
Andreas HermleTeam leader

Author

Commented:
Great job, thank you very much. Regards, Andreas
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Andreas!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial