Variable Print Area

[BongoBill]

Hi, I'm using Excel 2003.

I have a spreadsheet used for costing jobs giving a detailed breakdown of parts and time. Sometimes the customer requests a copy so I'm creating a macro for my boss that prints the spreadsheet, less the hidden columns that I don't want the customer to see. This I can manage.

My question is this: Is there a way within the macro to set the print area to change to where the last cell is automatically?

Each spreadsheet is a different length dependent upon the work required and setting a larger print area to compensate would use excess paper and ink.

Any ideas?

[shg]

Maybe

Please Login or Register  to view this content.

[BongoBill]

Thanks shg, although I'm still not quite sure where I'm going wrong. I attach an example of the spreadsheet which includes the macro.

Ideally, what I'm trying to achieve is to print columns B to N but only down to the last cell relating to either column C or M (which ever is the lowest row of the doc).

Does the script you suggested include the colour formatting in the and if so, is there any way to stipulate that the command only apply where cell contains text/number?

Any advice appreciated

[shg]

Try this code instead:

Please Login or Register  to view this content.

[BongoBill]

That's a huge improvement, thank you.

When printed though it goes further than the last text/number and continues to the last coloured cell. Is there any way to confine this script to text/numbers?

[BongoBill]

Okay, as this doesn't seem to be possible, I've removed the colour and borders and tried to edit the script accordingly. Unfortunately, when run it returns a compile error: invalid or unqualified ref near the bottom at .Hidden =

Please Login or Register  to view this content.

Can anybody tell me where I'm going wrong? I attach the revised example for ease of understanding.

Please Login or Register  to view this content.

[shg]

BB, please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

[BongoBill]

Changed code tag. Thank you.

[shg]

1. You have spurious values in what should be empty cells. Delete all the rows below the data, and then restore formatting to whatever the last used row is likely to be.

2. You missed deleting the corresponding End With

Please Login or Register  to view this content.

[BongoBill]

Re:

1) Still having problems with a few spurious cells after deleting but confident now that I can resolve it.

2) D'oh! It looks so obvious now.......

Many, many thanks for your help. I must say as well, that some forums treat noobs very rudely, I have never had that experience on this site. You guys rock!

[shg]

You're welcome.

I must say as well, that some forums treat noobs very rudely, I have never had that experience on this site

Serendepity. I'm on my meds today.