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.
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 WorksheetDim LR As LongDim Rng As RangeFor 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 IfNext WsEnd Sub
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?
Open in new window
Change the column A to actual column which has last row, accordingly adjust Rng