Non-contiguous Print Area Macro

[gatorchomp85]

Hey all,

I have a document which will require signatures after printed and want to keep the signature portion at the bottom of the page (currently rows 102-104). The problem is that the document will not need to be that long for every person and was wondering if it would be possible to have a macro that checks (C7:C:100), stops printing when it reaches the blank cells, and then prints those last 2 rows?

The goal is to design a mileage log for company vehicles which employees will have to sign and submit at end of month. Obviously, not every employee will be driving the same amount so some will require fewer rows than others, and in the interest of not having extra pages print out I was hoping this print macro could be done.

Thanks for any help.

[Leith Ross]

Hello gatorchomp85,

The Print Area function only prints contiguous rows. Is the page printed in black and white? If so, I have an idea. A macro could temporarily change the color the unused rows to white. The rows won't print but the print area remains contiguous. Can you post a sample of the mileage log?

[gatorchomp85]

I'm thinking maybe a BeforePrint action to delete the unused rows would probably work best. I'm just not exactly sure of how to write it to check only column C and then not delete the last 2 rows.

I attached a sample of what I'm working on if you want to look.

[Leith Ross]

Hello gatorchomp85,

Thanks for posting the workbook. If you delete the rows, you will no longer have a contiguous range of rows and the last 2 rows would be printed on a separate sheet.

[gatorchomp85]

I'm slightly confused. Say for example I only had mileage information that took me to row 50. If I delete rows 51-100 wouldn't that move rows 101-104 up to just after 50? From there I don't think I would need to worry about setting the print area, right?

Sorry if being a novice is showing through, but I'm trying to gain a better understanding of why there couldn't be a macro to delete those rows with empty cells in column C.

[Leith Ross]

Hello gatorchomp85,

I have the macro almost working. It makes a copy of the log sheet and clears all the data and formatting from the last mileage entry row + 1 to row 100. The problem now is the removing the Check Boxes. I assume you want the last 2 rows to be at the bottom of sheet, yes?

[gatorchomp85]

Yes, the last 2 rows are fine at the bottom of the page, or at least at the end of the log printout (but not behind the summary sheet).

I hadn't thought far enough ahead to dealing with the extra check boxes. Can a macro be easily written to delete them or change the formatting so the lines are white and they don't show up when printed?

Those check boxes have really been a bigger pain to the deal with for this project than I originally thought they would be. The ultimate goal was for this form to be as automated as possible and operate under the assumption that the user would not be savvy in Excel or computers in general. If you can see what I was trying to accomplish by using them and have a better suggestion for doing it then I'm all ears.

Thanks for the help.

[Leith Ross]

Hello gatorchomp85,

Got it figured out and working. I removed all the check boxes and replaced them with WingDing 2 characters that look like an empty box and checked box. The user just double clicks to either check or clear the box. The sheet is then copied and unsused rows are cleared and deleted. There is button on the mileage log to print the sheet. The attached workbook has all the changes installed.

Worksheet Event Macro to Check And UnCheck

Please Login or Register  to view this content.

Macro to Print the Log

Please Login or Register  to view this content.

Module1 MAcros
These macros were used to remove the Forms check boxes and replace them with the font check boxes.

Please Login or Register  to view this content.

[gatorchomp85]

I had to be out of the office yesterday and apologize for the delayed response. This is great!! I would have never been able to come up with this if I spent the next 2 months on it. Thank you so much for all of your help!!

[cyee]

Hi Leith Ross,

I'm actually have a same problem with gatorchomp85. But the problem is I'm know nothing about VBA. Could you help me on my problem?

I have this Export Form (as per attached).
The user only allow to key in 2 grade (Row 20 & Row 23) each time. So, if there is 4 grade to be key-in. User have to copy the whole form into sheet2 & key the same things.

Is it possible to use macro to create "INSERT ROW" (repear row20 : row26) to allow user key in the 3rd grade but when print out it must be 2 grade in 1 sheet. Its means exacatly as per my attached sheet.

Is it possible to prompt me the print window to allow me select printer?

BTW, if u able to help can u contact me at [email protected]

Thank you very very much.

Best Regards

[Leith Ross]

Hello Charlene,

Welcome to the Forum!

Since this is your first post, I don't expect you to have read through all the posting rules. Please start a new thread and include a reference to this one. Once a post has been answered, the forum doesn't allow for new threads to be started in an answered post. Once you have start a new thread, I will be able to answer your questions.

As a new member, please take some time to familiarize yourself with the rules.
Forum Rules

[jolivanes]

Leith / gatorchomp85
Would another way, see attached, be another option?
Put it in the left and center footer and increase the bottom margin to accomodate it.
Would this be a viable solution Leith?

Thanks and regards
John