Excel is used primarily for data analysis and presentation. Sometimes, your data may have text that is too long and could limit the printing space. office.com/setup
This article will explain how to calculate the right column width so that the text fits within the page. One example scenario is to have two columns standard and one column that displays comments.
Name, Date, Comment Excel offers many tools, but nothing beats being in control of your application and getting the results you want.
We will write code to calculate the width of available printing space. This will allow us to determine column widths for efficient printing.
Calculating the Available Printing Space on The Page
One problem is that the measurement units for page size, margins, and columns are not consistent. We need to standardize these units before we can determine the right column width.
Converting all units to points instead of inches and centimetres is one way to go. Initial page size is returned in inches. Then, the page is converted to points. Left and right margins are already in point.
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. We'll use the cell 1 measurements to create a factor that converts the width.
The width in points is only read.
pts = columns(1).Width
Excel's measurement unit is 'width'
wd = Columns(1).ColumnWidth
Factor = pts/wd
col3 = col3/factor We now have the value for column three's width and we use it in the following code. Also, we set word wrap to true as well as the row height to auto fit.
Columns(3).ColumnWidth = col3
Although the calculations for page size, columns and other variables can seem a bit confusing, it is worth persevering. It is easier to prepare data for printing if you have a good understanding of the measurements of each element of a spreadsheet. www.office.com/myaccount