Best Page Fit

[Bill]

Hi All,

I know you can shrink to fit and stuff but does anyone know of a way, or any
utilities, that will optimise Excel column and row sizes for the minimum
number of pages?

Regards.
Bill.

 

[Bob I]

You can set it to 1 page by 1 page if you want in Page Setup.

 

[Bill]

That's not what I meant. I want something that fiddles with all the column
widths and row heights and optimises them for least pages.

Just wondering - maybe there isn't anything.

Ta.
Bill

 

[AltaEgo]

Do you mean

Sub Macro1()
'
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
End Sub


Note that this is a recorded macro.

Look up "Change the column width to fit the contents" in HELP. Row height
works the same way.

 

[Bill]

Nope, you can do that manually quite easily.

Doesn't have the desired effect.

 

[Gord Dibben]

What would be your interpretation of "optimize".

You have rejected autofitting rows and columns and print to fit.

I don't know what's left.


Gord Dibben MS Excel MVP

 

[Bill]

What I should add here, that I have not done previously and consequently
this may have been confusing (apologies if that has been the case), is that
I am referring to spreadsheets that contain a lot of descriptive text rather
than just numbers. Consequently poorly adjusted cells could be very deep or
very wide!

My definition of optimise would be as follows.

On a large multi page spreadsheet, obtaining the optimal relationship
between column width and row depth throughout the sheet such that the
minimum number of sheets of paper are required when it is printed.

For example by manual inspection you would not have a column so narrow that
for some cells it needed to be very deep. Nether would you have that column
so wide that all the other columns have to be very narrow (and therefore
deep).

I have played at this, I have tried auto fit and so on and at the end of the
day I have always had to apply common sense and manually adjust.


Maybe I am missing something ....

Maybe I should just carry on as I was doing it.

I just wondered .....

Regards.
Bill.

 

[Gord Dibben]

Unfortunately row height and column width are properties of the entire row
or column.

10 characters in A1 and 45 characters in B1 with auotfit will give you
either a wide column based on B or a tall row based on row 1

No way around it.

What you're looking for is an individual cell adjustment which Excel won't
give you.


Gord

 

[Bill]

I knew that! That is why I asked the original question!

 

[Gord Dibben]

If you knew that then why ask the original question?


Gord

 

[Gord Dibben]

I hesitate to mention this but for presentation only, have you investigated
the "merge cells" feature?

You can make fat cells out of skinny ones.

I used the word "hesitate" because of all the problems merged cells can
create with copy/paste/sort/filter/autofit and great host of other
operations.


Gord