Dynamic print range

I have spent the last two days trying to get a dynamic print range to work but im not having any luck. If anyone could take a look (to save me from pulling out my hair!) I would much appreciate it.
Im trying to create a dynamic pint range for the Sheet 'Search'. The contents of which varies by the search criteria entered in the top search fields.
Eventually I want to create a macro to print, and a macro to make a PDF of the search results.
Many thanks in advance for taking the time.
James

Hi,

To alter the print range using VBA, use the following code (altered for your specific circumstances):

Please Login or Register  to view this content.

To make the print range dynamic, create a variable that will find the last row that has a non-blank value in it, and reference this to be the last row that is defined within the print area:

Please Login or Register  to view this content.

Hope this helps

Thank you very much for your response. The VB Dynamic print range code worked a treat!
Is there a way I can set the top rows (with the document title and column headers) to be at the top of every page? B10-J12
Also is there a way in the code to set it to always print out in Black and White (I have tried selecting this function in Page Setup - Sheet, but it seems to revert back and become unselected every now and then), resulting in the cell background colors being printed out.
Many Thanks,
James

Hi,

The black and white property is printer-specific, and can not be changed in VBA, as the printer preferences will always override. So short of you running a macro to change the colours to black and white prior to printing, and then changing them back to colour again after printing, this one isn't going to be possible sorry.

To get the headers at the top of every page, I would suggest running several print macros, rather than one single piece of code. So it would go something like this (pseudocode-ish):

Please Login or Register  to view this content.

Hope this helps

Thanks, that makes sense. Im just not sure what the code would be (especially to including the first code you gave me that finds the last row of data). The rows to repeat at the top are B10:J12. The first page is B13-J35 (plus the top 3 rows header rows). Every page after that is 23 rows of data (plus top 3 rows) so B36-J58, B59-J81 etc etc (the doc ends at 702 which is 30 pages).
Thanks,
James

Sorry, wrong thread

Will get back to this thread in a moment

Hi,

Try this:

Please Login or Register  to view this content.

This finds the last used row first, then for each sheet finds the last row for that page. It then prints out that page, then hides those rows. Then if it has printed all of the used rows, all rows are unhidden and printing is stopped (so that you don't print out any unused rows).

Hope this is what you're looking for

Is there a way I can test it without having to print it out (can I just view the PDF preview or something).

Unfortunately, if you want the headings at the top of every page, the way this has to be done is to print x number of 1-page documents, rather than 1 x-page document.

What I'm getting at is, yes you can export to PDF instead of printing while you are just testing, but you won't get a single PDF file, you will get multiple single-page PDF files.

Instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

Let me know how this goes

I replaced it with that code (just changing the directory to be mac friendly), but I get run time error 1004 on that line.

Hmmm, OK I guess I should have looked at the fact that you are using Excel Mac.

Here's a file with that line of code that works on my computer (using Excel 2010 for Windows), can you please tell me if it works for you? It should save 2 PDFs ("Page01.pdf" and "Page02.pdf") into the directory where you save this attachment.

If it doesn't, it is more than likely an incompatibility with the Mac version of Excel.

Thanks

Didnt work, its coming up with the same 1004 error.

OK after some looking around, I'm hoping this may fix your issue...change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

on that line of code only.

Let me know how you go...

Or if this doesn't work, I believe that there is a "Save As PDF" option built in to Excel 2011 Mac in the printing dialog.

So perhaps try setting this as the default printer in Excel and then executing the .PrintOut code from a few posts above.

Failing this, I really can't help with this part of your problem sorry, I've never used Excel Mac and have never debugged any issues in it either.

Actually, going back to the basics here, but have you triple checked that your file path is correct? As I believe an invalid path would produce a 1004 error...

Sorry its taken me some time to get back to you. I couldn't get the PDF print to work, so I just had to keep printing on paper. A couple of trees later I determined it doesn't quite seem to be working. It keeps coming up with errors on this line:

endPage = (23 * i) + 12

And it tends to print alot of blank pages with just the header and footers.

Could you please upload an updated version of your workbook with all of this code in it so that I can debug it please? This all seemed to work fine in the mock-up workbook that I made before suggesting it, so I would like to see why it isn't working in your workbook.

Thanks

Thanks Richard, I knew the Windows path syntax wasn't the same as the Mac path syntax, but having never used a Mac (n00b, I know! ) I wasn't sure what the difference is.

I still think the reason for the 1004 error is an invalid path, but this doesn't explain why

Please Login or Register  to view this content.

would be throwing an error...which is why I am keen to see how the code has been implemented...

Well after some tinkering I think its now working for me. I removed the top three rows (the column title and document title) from the print area in the code, and added them to the repeat print rows in Page Setup instead, which helped. I also manually moved around some of the print lines.
I used the same code along with Ron de Bruin's PDF email code, to email pdfs.
Thank you for all your help, and for sticking with me on this issue.

Glad to hear you got it working, and I am happy to have helped.

Please don't forget to mark this thread as solved and please click on the * next to my post(s) to say thanks

Sorry im new to this forum. How do I mark it as solved?