asked on

Excel 2007 - setting printing area to dynamically recognise end of data.

I currently have a series of command buttons that initiate the printing of print areas within a single worksheet.

Many of the print areas have large numbers of rows which are reserved for possible data to be inserted.

All bar one of the print areas are to be set at one page wide.

The length of the print will be data dependant based on the length of the wage assessment period and the industrial instrument being used.

The columns are static.

I am hoping that there is a way to have excel automatically set the print area by recognising if there is any data in the first row of the first column of the print area.

If there is no data in the cell then the print area finished at the previous row.

The current code that i am having problems with is

Sub MyPrint()
Dim i As Integer
i=101
While Cells(i, 0) > "" 'while will loop until condition is met..
i = i + 1
Wend 'wend is while end, end of loop everything between while and wend is executed
i=i-1
CurPrtArea = ActiveSheet.PageSetup.PrintArea
If Range("linkcellfromCBox") = True Then
myPrtArea = "B101:D" & i
With ActiveSheet.PageSetup
.PageSetup.PrintArea = myPrtArea
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintOut
End With
End If
'
'~~>

I have attached a file that I have been using.

The print area is B101:d131

Would appreciate some help in solving this issue.

Regards,

Andross9
Setting-Print-Area.xlsm

philip m o'brien

A couple of issues straight off:

The code
cells(i, 0)—this indicates row i and column 0. This must be at least column 1 i.e. cells(i, 1)

The code
With ActiveSheet.PageSetup .PageSetup.PrintArea = myPrtArea—this includes ".PageSetup.PageSetup" which will fail

Andross9

ASKER

Thanks SubversiveArmadillo

the code

With ActiveSheet.PageSetup
.PageSetup.PrintArea = myPrtArea

this includes ".PageSetup.PageSetup" which will fail

I am confused with this comment - can you expand?

What should it be?

philip m o'brien

Use:

With ActiveSheet.PageSetup
    .PrintArea = myPrtArea
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintOut
End With

Open in new window

Andross9

ASKER

Thanks again but still no luck.

Have tried in the file I sent to you and also in the attached file which has cbox

Have spent the last hour playing with changes to cide etc - finding context and practice of VBA hard to get my head around.

Can you help me with my less than satisfactory attempts

Andross9

Andross9

ASKER

Help if I added the file updated-Printing-Area-Locations.xlsm

philip m o'brien

Final couple of points.
In your table you have entered b101"b131 in cell C3, which I assume is a typo. This mis-references and will fail.
The other point to bear in mind is that print areas need to be set at a worksheet level e.g. Sheet1!B101:B131 etc
So you have a few ways to handle this:
Include the sheet detail in the Print Area column; add an additional column to contain the sheet number and update your code to handle this; amend the code directly e.g. if they are all in sheet1 you could use:

myPrtArea = "Sheet1!" & cb.TopLeftCell.Offset(0, 2)

Open in new window

etc
Other than that there is nothing wrong with the code you now have.
Regards

Andross9

ASKER

Thanks for your support,

Have made those suggested changes and still find that I get an error message

Object doesnt support this property or method

Thanks for trying to help here - might go back to the basic solution and print heaps of balnk pages.

Once again thanks for your efforts.

Andross9

philip m o'brien

No problem. Have you got details of where it's failing? I used your code and it was working for me.

Andross9

ASKER

I have kept playing but this is where I am at.

The checkbox approach by itself works fine.

When I try to add in the dynamic print area setting - I have all the problems.

Regards,

Andross9
Frustrated-Printing-dynamically.xlsm

ASKER CERTIFIED SOLUTION

philip m o'brien

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial