How to compare the max width of a cell compared to the max width of awksht page?

  • Thread starter Thread starter Chet
  • Start date Start date
C

Chet

I am trying to cycle through a workbook and perform a pagesetup
command in order to prepare the page for printing. Some of my pages
will be 1 page tall & 1 page wide, and some will be 2 pages tall and 1
wide, and a few will be 3 pages tall and 1 page wide. (No problem on
the cycling through part.)

My goal is to use the code to prevent a small portion of my worksheet
from showing up on page 2 of a print-out when maybe by using
the .PrintArea command and the .FitToPagesTall command to control how
many pages the worksheet prints on.

My question is (possibly I am going about it the wrong way) but I am
using the
ActiveSheet.PageSetup.PrintArea = "$A$1:" & BotRightCell
command to set the print area. My problem is that the BotRightCell
does not extend as far out as some of the cells higher up in the
worksheet so when I use the .printarea command it cuts off this text
which extends further to the right.

I am also using the .pageheight command to set the number of vertical
pages I have with the following code.-
'GET NBR OF VERTICAL PAGES
VerticalPages =
Application.WorksheetFunction.RoundUp(PageHeight / 1100, 0)
If VerticalPages < 1 Then
VerticalPages = 1
GoTo Skip2
End If
If VerticalPages > 4 Then
VerticalPages = 4
GoTo Skip2
End If
As you can see below my sample data has the first row as "2 CHANGE
IMPACT AND ACTION REQUIRED" , but the other rows are much more
narrow. So If I set the width of the page on the very "right most"
cell then I will cut off the display of the first row because it is
simply wider than the last row.

I hope this makes sense.

Thanks,
Chet

Sample of what a page looks like. (All data in column A).
2. CHANGE, IMPACT, AND ACTION REQUIRED

A. Change:
Impact:
Action:

B. Change:

Impact:
Action:
 
I am not so sure that Excel was you best choice of applications to use for
what you are trying to do. It would have been much easier in MS Word.

BTW you will probably get an error message with the BottomRightCell
expression if you try to use it for a data column. It is a property of
Shapes, Charts and OLEObjects but not Range objects.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top