Resize column width based on page setup settings?

[Joel Mills]

I have a report that is setup to print out on 1 page. It is setup to fit
without scaling. Rows (the number of rows are constant) and Fonts are set
fit within the Header, Footers, and top and bottom margins. The number of
columns vary anywhere from about 25 to 55 columns depending on the data
imported. The left and right margins are set to .25 inch. The 1st row
contains the Titles which are Dates. Columns 1 & 2 are also Titles and
don't need any adjusting. What I do now is if the column range exceeds 25
then the horizontal alignment is changed to xlcenter. Then by trial and
error columns 3 thru 50 (this number varies) are manually adjusted to fit
on an 8.5x11 page set up in landscape. Using print preview the column
widths are changed until the report fits within the 1/4" margins. The data
in all the rows except the Title Row are two digit numbers and will fit then
the Date column is realigned and will not require any adjustment of Font
size. I'm not even sure if this can be done with VBA.

 

[Tom Ogilvy]

if all the columns (3 to 50 (this number varies)) contain the same type data
(2 digits) this would suggest that they can all be the same column width.
You could set your margins (this could be easily automated by recording a
macro), then go to View => Page Break Preview, select columns 3 to the last
one using the column headers at the top, then make slight adjustments to all
the columns at once (use the edge of any selected column -- mouse is double
headed left/right arrows icon) until all the columns fit.


This would take only a couple of seconds.

Are you aware of that and still think it needs to be automated completely?