printing spreadsheets with merged cells

[ecivon]

Hello,

I have a worksheet which is used for data entry by various users. It has a table with 3 rows per item which in some columns are merged and in others are not.
There is a button to insert new rows (of 3) to allow users to add new items to it when required.
I have successfully implemented an auto-height function in the merged cells.

The problem is, when it comes to printing the sheet, excel doesn't recognise the merged cells as a single cell and treats each row individually.

I need it to recognise these merged cells.

This is further complicated by the fact that the row heights can vary widely depending on the data input by the users so adjusting the margins etc is not an option.

Can anyone help?

Thanks in advance.

 

[Andrew Poulsom]

Please explain what you mean by this:

The problem is, when it comes to printing the sheet, excel doesn't recognise the merged cells as a single cell and treats each row individually.

 

[ecivon]

For instance, I have a merged cell like the one below. It consists of three rows. If this is at the bottom of the page, I might end up with half the text at the bottom of one page, and the rest of the text at the top of the next page.
This is because excel doesn't recognise that this is a merged cell and needs to be kept together when printing.
I would like the entire merged cell to be pushed to the top of the next page in such circumstances.
Unfortunately, as I said, due to the random data entry that could occur, setting margins manually is not an option.
Also, the sheet is protected and the users must not be able to edit anything other than the cells that I have left unlocked.


<TABLE style="WIDTH: 208pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=278 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD class=xl69 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 160pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 76.5pt; BACKGROUND-COLOR: transparent" width=214 colSpan=3 height=102 rowSpan=3>Only trained persons to use cranes and sling loads.
Inspecting lifting equipment and check inspection colour code before use. Report any defects.</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=64>Likelihood</TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 25.5pt; BACKGROUND-COLOR: #d8d8d8" width=64 height=34>Severity</TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: #d8d8d8" width=64 height=34>Score</TD></TR></TBODY></TABLE>

 

[Andrew Poulsom]

Excel takes no account of merged cells when setting page breaks - that's done row by row. If you want to avoid overspill you will need to set a manual page break.

 

[ecivon]

Can I put page breaks in in vba?

I guess I could think of some way of finding out if the text is going to spill over and insert the page break then.