Adjusting column and row height to fit one page

[EmilMK]

I am trying to make a macro that adjust the row and column height within the printarea so that they fit a certain number of pages before printing to pdf. The purpose of this macro is to generate the "same" pdf file no matter what computer (with differet resolution, print settings and so on) when printing.

The printing to pdf works well for me, but the fitting of the columns and rows not so much

I have realised that the following section adjusts the column width and that the input needed is in excel units:

Columns("A:HP").Select
Selection.ColumnWidth = x.xx

In my current setup the column width needs to be 2.14 (excel units). So far so good, now to determine what the width should be on any computer.

I get the page width in points:

pgWid = Application.InchesToPoints(8.267) '(8.267 beeing the width of A4 paper)

Subtract the margins and divide by 32 (beeing the number columns that I would like to fit on one page):

LeftMargin = ActiveSheet.PageSetup.LeftMargin
RightMargin = ActiveSheet.PageSetup.RightMargin
cWid = (pgWid - LeftMargin - RightMargin) / 32

Now I have the column width needed in points so I transform that to excel units by a factor:

factor = Columns("A:A").Width / Columns("A:A").ColumnWidth
cWid = cWid / factor

And apply the found width to the columns of interest.

Columns("A:HP").Select
Selection.ColumnWidth = cWid


What I get is a cWid of 2.0601 (excel units) for my setup instead of the needed 2.14. Same procedure is used for the rows (subtracting the header, footer, top and bottom margins from the page height of 11.692 inches) yielding a cHei of 1.841664 instead of 2.14 which is also needed here (50 rows on one page)

Margins are set to normal in the macro before any of the editing of the column and row width and height.

Any clue what I am doing wrong? Or if there is a better way to reach the same goal?

Regards Emil

 

[TonyUK72]

If your document is well designed can you not just make the printer print to the entire page, set your margins etc within the print dialog. This can be done with macro's also.

 

[EmilMK]

@TonyUK72, maybe I don't understand what you mean by well designed, but my problem is that the setup looks perfect on my computer but when I have a college open the workbook the column width and row height of all the cells change (due to differet resolution I believe) making the setup look like crap... So I wanted to make a print macro that changes the column widht and row height before printing so that the setup looks good again.

 

[TonyUK72]

Because your printing to PDF (or to paper for that matter), you can adjust the settings within the FILE - - -> PRINT dialog to ensure it always creates the same looking file to print. "You can record a macro to do this", the PDF will then be the same which ever computer it is generated from.

Adjusting row heights & column widths, so that your spreadsheet looks the same on ALL computers is somewhat irrelevant to the end goal of the PDF.