Because Excel is primarily an application for data analysis, sometimes its presentation tools suffer by comparison. For example, sometimes your data has text of various length and might exceed the available printing space.
This article will show you how to calculate the correct column width to ensure the text fits within the page. A typical scenario might be 2 standard columns and then a 3rd column displaying comments:
Name, Date, Comment
Although Excel has some tools available, such as the auto-fit feature there’s nothing like being in control of your application to get the outcome you need.
We’re going to write some code which will calculate the width of the available printing space, so we can determine the column widths to ensure efficient printing.
Calculating The Available Printing Space On The Page
One of the problems is that the units of measurement for page size, margins and columns are inconsistent and we need to standardize the units before determining the correct column width.
One way forward is to convert all the units into points, rather than inches or centimeters. The page size is initially returned in inches and is converted, while left and right margins are already in points.
pgWid = Application.InchesToPoints(ActiveSheet.PageSetup.PaperSize)
leftMargin = ActiveSheet.PageSetup.LeftMargin
rightMargin = ActiveSheet.PageSetup.RightMargin
Therefore, some simple maths will give us the available printing space and this is the width we need to fit our third column into.
content = pgWid - leftMargin - rightMargin
col3=content-columns(1).Width - columns(2).Width
Now we’ve got the width required for column three in points and that’s fine, except that column width is not measured in points, but in Excel’s own unit based on font sizes. So, we’ll create a factor to convert the width based on the measurements in cell 1.
' width in points - read only.
pts = Columns(1).Width
' width in Excel's measurement unit
wd = Columns(1).ColumnWidth
factor = pts / wd
col3 = col3 /factor
Finally we have value for the width of column three and we use that in the following code plus setting word wrap to true and the row height to auto fit.
Columns(3).ColumnWidth = col3
Columns(3).wrapText=true
Rows(3).EntireRow.AutoFit
Summary
While the calculations for page size and columns can be a little confusing, it’s worthwhile persevering. A good knowledge of how the different elements of a spreadsheet are measured enables easier preparation of data for printing.