Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Size to fit printer page (Excel 2k Win 2k)

    Hello All

    There is a spread sheet in use where I work that is used for reporting various accounting numbers. The sheet also has to contain information on actions in place to improve these numbers. The problem occurs when people print the sheets off. Due to the monthly variation in the quantity of text in each cell the height of the print changes to allow the print to fit on one page. Is there any way that VBA will allow me to alter the height and width of the cells I want to print to get the best fit for a page? If so I'll be a happy bunny, and a few more trees will be alive this time next month.

    I've used VBA lightly in Access, but never in Excel, other than altering the occasional recorded macro, so please be gentle with me.

    Thanks for the help

    Ian

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Size to fit printer page (Excel 2k Win 2k)

    The only way I envision (if I understand correctly) is to vary rowHts and colwidths and see what the zoom is to print on one page and then pick the setting with the highest ZOOM value.

    I don't think this can be done without creating some algorithm to work this out completely generically. If only certain columns tend to be wide you could concentraate on that and have rows autofit and then only vary the column width of those columns and check.

    You might be able to work on some "simulation" using the text length (those the font will affect the col width) to determine how affect of col width vs row height and act accordingly and try to calculate it.

    I don't it can be don directly and to even try to approach a solution will involve more details an extremes in the cols and rows

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size to fit printer page (Excel 2k Win 2k)

    Steve

    I had a feeling it wouldn't be a simple sort of fix, ah well guess I'm back to relying on people using print preview and then altering the column widths to sort it out.

    Thanks for the information

    Ian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •