Resize column width based on page setup settings?

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.

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?

--
Regards,
Tom Ogilvy

"Joel Mills" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

I'm looking for a similar solution. Any ideas for automating? My data is slightly different. I make financial statements in Excel 2007 and I use 4 narrow columns down the left to control indentation needs for line titles. The fifth column is wider and is used for spillover line titles. Then I have my balance columns on the right side of the page. I'd like to set my templates up to scale the size of that middle column based on the page width, margins and number of numerical columns presented at the right. Is there a way to automate this? Any ideas welcome! Thanks!