We help IT Professionals succeed at work.

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

144 Views
Last Modified: 2017-03-17
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

Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
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
CERTIFIED 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.
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
CERTIFIED 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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Andreas!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions