Exclude printing Columns that don't contain Values

**PW6780** · 03-02-2012, 02:16 PM

Hi All,

Forum Sheet.xls

I've attached my spreadsheet, I need some help with.........

Is it possible that when i print the attached speadsheet, if any of the columns have no value [empty] that those colums won't print?

The exception would be Columns A, B, C & J [Month, Tax1, Tax2 & Total] these would always need to be printed.

If it could be done, in my spreadsheet example, columns headed: Tax3, Tax5, Tax7 & Tax8 would not be printed as those colums contain no data.

I've re-written the above a few times, just hope it makes sense.

Thanks
PW

**dilipandey** · 03-02-2012, 02:24 PM

Hi PW,

You can hide the columns which have no values to avoid them to get printed and for this a quick macro can be used.

Regards,
DILIPandey

<click on below 'star' if this helps>

**PW6780** · 03-02-2012, 02:36 PM

Many Thanks for the reply.

I understand that I can hide the columns myself but ideally looking for a way that excel can do this for me and take out the human error side of forgetting to hide or worse hiding a column that does have data.

Idealy this spreadsheet would act as a template and we would used it for different clients [1 x workbook per client].

How would I go about using a Macro if the columns that may be empty could vary from client to client.

Regards
PW

**dilipandey** · 03-02-2012, 03:00 PM

HI PW6780,

Before printing, just press Alt+F8 and run the macro "hide"

copy the below code in any module of your workbook. Press Alt + F11 to open VB window, Alt + I +M to create module, and paste below code in the window. thanks.

Please Login or Register  to view this content.

Regards,
DILIPandey

<click on below 'star' if this helps>

**PW6780** · 03-03-2012, 04:26 AM

Hello, I've followed the above instructions but when running the macro, I get the following error:

Complie error: expected End Sub.

I guess, I've done something incorrect, any ideas what i've done wrong anyone??

Thanks
PW

**dilipandey** · 03-03-2012, 04:31 AM

HI PW,

Have you copied the code till the last line i.e., "End Sub" ?

Regards,
DILIPandey

<click on below 'star' if this helps>

**PW6780** · 03-03-2012, 04:53 AM

Hi DILIPandey

I've re-copied and i'm now not getting the error message, thankyou I must have incorrectly copied the code.

But the macro still doesn't seem to be working...I've attached my revised sheet with the macro installed.

Any ideas what else I may be doing wrong.
Forum Sheet_With Macro.xls

Thanks for your time.

PW

**dilipandey** · 03-03-2012, 06:02 AM

Hi PW,

Below are the issues, which needs to be addressed before running the macro.

Row 1 is merged till column L
Row 4 is merged till column L
You do not have any blank columns... if you column d and h as blank then I must say - they are not as they have some text written in D7 and H7 respectively.

If there points would had been clear in your initial posts as well, I could have considered these points while writing the macro. Suggest you to please think once more and let me know these kind of points in advance (which is not mentioned in your posts), so that the next solution is complete and error free. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**PW6780** · 03-03-2012, 06:22 AM

Hi DILIPandey

Apologies, after reading your post, I understand my initial post certainly wasn't clear enough, Let me try again.

I have removed the merge cells and can confirm that the data will only ever be input into cells B8:I19,the rest of the sheet, none of the data will change.

So the macro would need to hide any columns between the range of D8:I19, that don't contain any data in them cells.

So as per my revised attached spreadsheet, columns Tax3, Tax5, Tax7 & Tax8 would not be printed as those columns contain no data between rows 8-19

I hope that is better to understand.

Thanks
PW
Forum Sheet_With Macro_no merged cells.xls