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!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
@Shums
I think you misunderstood the requirement. :)
The requirement was like this...
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.
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
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?
BTW what's not working at your end?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial