Help how to limit the number pages VBA converts excel into PDF

[bdouglas1011]

I have a sheet I am using VBA to convert an excel sheet into PDF and attach it to outlook. If I take the same sheet and manually create a PDF it is only ten pages the amount of pages the excel files has values in.
When the VBA does it the PDF file is 45 pages long.

How can I limit the PDF pages or get it to only create PDF of cells with values in it. Here is the code.

Please Login or Register  to view this content.

[Kenneth Hobson]

Without seeing the data, I don't know. Have you checked your PrintArea(s)? e.g.

Please Login or Register  to view this content.

Maybe record a macro and compare to your manual code?

I do have some suggestions otherwise.

1. Use Sheet's Protect with UserInterfaceOnly:=True rather than Protect and UnProtect. I normally do that for all sheets in the ThisWorkbook's Open event.
2. Seldom is Activate and ActiveWindow needed.
3. Change the Copy to just the UsedRange. Normally, Column A should never have blanks for data. The first blank should be the end of the data for all rows. Of course not knowing your data, that can change. Coupled with (2), you could turn 5 or 6 lines into one long line.

If you want to try any of the suggestions and need help, please post back. Always test on backup copy. e.g. Some of the suggestions, do not run unless you understand each part.

Please Login or Register  to view this content.