PDA

View Full Version : Height of Prina Area



blis
07-06-2011, 03:20 AM
Hello all.

I have a task to make all worksheets the same height.
I wrote macro to adjust height of last row in print area. Code is below or in sample workbook I uploaded.


Sub Page_Height()

For i = 1 To Sheets.Count
Worksheets(i).Select
WS_name = ActiveSheet.Name
With Worksheets(i)
Set rngParea = .Range(.PageSetup.PrintArea)
P_height = rngParea.Height
Diff_P_Height = 852.75 - P_height

Last_Row = Range(Split(ActiveSheet.Range("Print_Area").Address, ":")(1)).Row
Range("e" & Last_Row).Select
Rows(Last_Row).RowHeight = Rows(Last_Row).RowHeight + Diff_P_Height

ActiveSheet.PageSetup.Zoom = 94
End With
Next i

End sub


Macro reads the momenterely height of print area and then compare it to optimum height (its 852.75 points) to get the difference between heights.
This difference is used for adjusting the last row height.
The problem is that heigth of sheets is not the same on all sheets and I can't figure it out why. The difference is best seen in bottom right corner wher arow is located. Arow is moving up and down for a bit.
Can anyone help me understand where I went wrong with this macro?

I would really appreciate any help (because this problem is driving me mad) and sorry for my bad english. I hope my writing is understandable.

Thanks in advance.