[macro] resize selection, save as pdf

[LibreGuy]

Allright guys. I’m getting the hang of Calc It’s doing some nice things for me. But this time I’ve got a real difficult question … I think.

So here’s the scenario. I created a spreadsheet. The first 5000 rows contain formulas to help me fill in the data. Each event I want to register, contains 1 row. So for example, when there are 10 events, it will take exactly 10 rows to register them. This means after 10 events, there will still be 4990 empty rows containing only formulas.

Now I do have 2 problems:

1. When I export my spreadsheet to a PDF, the empty 4990 rows will also be exported. This is useless.

2. Each row fits perfectly on my screen, but is too big (even in landscape mode) to fit on a single page. This means that each row will be printed over multiple pages.

Now to prevent these 2 problems, I wonder if it is possible to manually select the first 10 (non-empty) rows. Then run a macro that A) “shrinks” (the width of) the content in the selection so it fits on a single page and B) export the resized selection (without the 4990 empty rows) to a PDF file.

Probably I’m asking something that is totally impossible, but I’ve seen strange things happen on this forum, so who knows Anyone got a solution maybe? Is what I want possible (or is there maybe a betters/easier way)?

[DroidTomTom]

Add a second sheet to your spreadsheet called Sheet2 that references the cells from Sheet1 that you want to show. Adjust the rows and columns to fit a page (basically creating a print template). For really wide cells set them to wrap the text in ‘Format Cells…’. Define the Print Ranges for Sheet2 to fit the referenced information by selecting the cells you want to be printed then choosing ‘Format’ > ‘Print Ranges’ > ‘Define’. (‘Define’ deletes any existing print ranges). Now when you go to ‘File’ > ‘export as PDF’ select the ‘General’ tab in the PDF Options dialogue box and set ‘Range’ to only the pages you want exported to the PDF document.

This preserves all the functions and formatting on your worksheet in Sheet1 while giving you a printable template in Sheet2. It is not dynamic but it gives you much more control.

[PYS]

Hi

If formulas display nothing when there is no data in the other cells, e.g. =IF(C2<>"";C2*3;"") in the screenshot below

what you ask could be quite simple without macro.

• Shrink the width: Format▸Page▸Sheet tab▸Scaling mode: Fit print ranges to width/height▸Width in pages: 1▸Height in pages: 1000
• To select non-empty data: Find & Replace▸Find .▸Other options: Regular expressions▸Search: Values
• File Export to PDF▸Range: Selection

Of course the first step is to do only once…

Regards