Column width different when printing worksheet

Originally Posted by Scuba_Steve

Using Excel 2003.
I have researched this issue and found some references to changing to monospace font, changing normal style sheet etc. but nothing seems to work consistently.

I use Excel for reporting on long filenames/pathnames. I import the file lists, shade the first column to represent the maximum path length of 255 characters and then send the report to the user department to shorten any of the files that are longer than the shaded first column (make it easy for them to see what needs to be shortened).

I have several problems with this.

1. I would like to be able to setup a template or something that automatically sets the first column width to 255 characters and shades it. I have not found a way to consistently set the column width at 255 actual characters (in the filename) as it seems to change based on the worksheet contents. I have tried using different fonts (TT and monospace) but each time I still have to go into the worksheet, count 255 characters in one of the lines and move the column width to that point. Is there a way to do this and if so what font and/or settings do I need to use?

2. When I have the first column width set to the proper value (when viewing the spreadsheet) it does not have the same column width when printed out. This is confusing to the users as the shaded area is different when viewing vs. when they print it out. As mentioned above, I have tried all the tips on the Microsoft site etc. but have not been able to consistently have the view and print column widths be the same. Is there a way to do this and if so what font and/or settings do I need to use?

Thanks in advance.

Hi,

I'm not quite sure why you are doing what you are doing, but having only partial details doesn't help my thinking.

Firstly I would consider using a separate report for long filenames, set a spare column to =If(Len(A1)>255,"y","") and filter on the 'y' to produce a 'to be changed' report, this could be done by macro, with an added line
Columns("A:A").EntireColumn.AutoFit
to ensure the column width suited todays requirement.

To your question, is your 'default printer' the printer you are printing this report to?

Does the 'Print Preview' match the worksheet or the printed output?

Why are you 'counting 255 characters'? why not let Excel count the characters and you simply select a row that has 255 (or close to it).

If you really must shade the items, why not use Data, Conditional Format on the column A with a formula =Len(A1)>255 and set a required shade?

If you are setting the width of column A why not 'Autofit' the column (double click the right-hand boundary of the column header)

If you are not using a monospaced font then setting the column width to 255 character will prove difficult for you, I offer no advice as I see no reason to do this, it appears contrary to what your report is about in that the column width sets the bounds for the cell, if you have data in column B then the cell will be truncated to it's print limit thus chopping off the rest of the characters, ie, the filename, the very thing that you want the user to change.

My suggestion is that you set your column width to the width of Landscape paper and print as a separate report, this should overcome all the difficulties that you appear to be setting yourself.

hth
---

of course, in a paperless, environmental friendly, office you would email the required person with their portion.

---