Using VBA To Determine The Page Size And Required Column Width In Excel


Because Excel is mostly a program for data analysis, some times its demonstration tools have problems with contrast. As an instance, some times your computer data includes text of numerous length and may possibly transcend the available printing distance kate meckler.

This guide is going to explain to you how to compute the right column diameter to guarantee the writing fits inside the webpage. A typical situation might be two regular columns after which a 3rd column showing remarks:


Even though Excel has any tools available, like the auto-fit feature you’ll find nothing like being in charge of one’s application to find the end result you require.

We will publish a code that may figure out the diameter of the readily available printing distance, thus we can ascertain the column widths to guarantee efficient printing.

Among those issues is that the components of measurement such as page size, columns and margins are somewhat inconsistent and we will need to standardize the components before deciding on the right column diameter.

1 of the ways forward would be to convert all of these components to things, in the place of a inches or centimeters. The page size has been initially came back in inches and can be changed, while left and right borders are already in points.


So, some basic maths can provide us exactly the available printing distance plus this really is the diameter we will need to fit our next pillar right into.


Articles = pgWid – leftMargin – rightMargin

col3=content-columns(inch). Width – columns(two). Width

Now we have the breadth required for pillar three in points and that is ok, except the pillar diameter is perhaps not quantified in points, however in Excel’s very own apparatus predicated on font sizes. Thus, we will create one variable to convert the breadth dependent on the dimensions in cell 1.



‘ width at Excel’s dimension unit

wd = Columns(1)). ColumnWidth

variable = pts / / wd

col3 = col3 /variable

Finally we’ve value for the diameter of column three and we utilize that at the subsequent code and setting word wrapping to authentic and the row height to match.


Columns(3). ColumnWidth = col3

Columns(3). WrapText=authentic

Rows(3). EntireRow.AutoFit


As the calculations such as page dimensions and columns are sometimes somewhat perplexing, but it’s rewarding persevering. A great understanding of the way the varying facets of a recorder are quantified enables more straightforward preparation of data to printing.

You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *