Fitting fixed number of columns to pg size for printing

[Dimaa]

Hi all,

I'm struggling with getting my Excel worksheet to fit nicely on the pg for printing to .pdf. I could tinker with column widths manually to work this out for a printed report. But I need to print a couple hundred reports with varying column widths, so I need a vba solution.

The context is that I'm using vba to process two sets of interlinked data on a worksheet. I only want to print one set of data, and keep the second set out of the print view. To do this, I'm keeping the first set of data in columns 1 to 10 and the second set of data in columsn 21 to 30. I set the print range to only include columns 1 to 10.

This works fine for keeping the second set of data off the printed pg. The problem is that there is always a gap between the end of column 10 and the maximum width of the printed pg. Given the formatting of the report, this doesn't look very good.

I know that page setup offers the "fit to" option. This would work if I wanted to fit everything on the worksheet on the pg, but I don't. I just want to fit columns 1 to 10 to the pg. Is there a way to do this, allowing for varying widths in columns 1 to 10? Is there a way to do it via vba?

Thanks for your suggestions.

 

[indiantrix]

I may be missing something, but why not simply select your first ten columns, then print selection? That would eliminate any extra blank columns from the print job. That other data out in "right field" will remain with the Excel file, but not show up in the PDF.
Larry.

 

[Dimaa]

Thanks for replying.

The problem is not with selecting the columns for printing, and I'm not printing any columns I don't want. But when I print the few columns I selected, there is a gap between the right-most columns and the right edge of the .pdf.

Imagine my columns were filled with some color. On the right edge of the printed .pdf, there is a long strip of white extending down. It's not a blank column. The problem is that Excel is not fitting the selected columns to the pg width.

Does that help? Again, I'd use the "fit to" option to solve this if I were printing the full worksheet. But I don't know how to do this when I'm only printing a select number of columns.