PDA

View Full Version : Printing With Merged Cells



MWE
09-07-2006, 09:34 AM
I have a worksheet with some areas where cells are merged vertically. When I print the worksheet, some of the page breaks go through the merged cells and I end up with some text from that cell on page n and some on page n+1.

Is there a way to tell Excell to not generate page breaks through merged cells? I can manually move the page breaks using the Page Break Preview tool, but if the contents of the sheet change a bit, I have to do that again. I checked the VBA-X KBase and found nothing to help me so I roughed out some VBA code to do what I want, i.e., insert page breaks in such a way that merged cells are not split. It will work, but is messy and will likely get confused if I change printer settings. So I wondered if there is a simplier way ... perhaps an option I have not found.

Thanks

Ken Puls
09-07-2006, 10:38 AM
Not that I'm aware of. Personally, I'd work out the max rows per page, and loop down the sheet. If x rows from last page break is a merged cell, set your page break above that cell and continue on.

Set it up to do it in the Before_Print event, and you shouldn't have to worry about it. It ain't perfect, I'll grant you, but it would probably work.

MWE
09-07-2006, 10:47 AM
Not that I'm aware of. Personally, I'd work out the max rows per page, and loop down the sheet. If x rows from last page break is a merged cell, set your page break above that cell and continue on.

Set it up to do it in the Before_Print event, and you shouldn't have to worry about it. It ain't perfect, I'll grant you, but it would probably work.
thanks for the reply. Your suggestion is approx what I have done. I ran into some problems getting rid of automated page breaks when I had the code as part of Before_Print, so I run the vba PageBreakMaker before I print and it works OK. I was hoping for a more elegant solution that would accomodate changes in printers, etc. It is interesting how these types of issues have not been resolved by MS; seems like such an obvious problem.

Ken Puls
09-07-2006, 10:54 AM
LOL! I see merged cells as an obvious problem, personally, and am surprised the MS hasn't had the good sense to eliminate THEM. ;)

I'm surprised on the before_print that you could wax page breaks though. What about this...

-On workbook open, wax the page breaks
-Before print Set your pagebreaks via loop
-Print
-Remove the page breaks again (Is this an issue though...?)

You should also be able to set up a routine, I would think, that deals with the printer issue. Even if it is only to set a page break, check what row it is in, write that to a custom document property for safe keeping, and remove the pagebreaks again. Probably another job for the before_Print routine, as you'd want to capture it if they changed printers during their session.

Food for thought...

Murphy will have it though, that as soon as you mock all that up, someone will come along and share a way to do it easily. ;)

MWE
09-07-2006, 01:56 PM
LOL! I see merged cells as an obvious problem, personally, and am surprised the MS hasn't had the good sense to eliminate THEM. ;) I do hope that MS does not act on your suggestion. I find merged cells to be very valuable and have been willing to put up with the quirks.


I'm surprised on the before_print that you could wax page breaks though. What about this...

-On workbook open, wax the page breaks
-Before print Set your pagebreaks via loop
-Print
-Remove the page breaks again (Is this an issue though...?)

You should also be able to set up a routine, I would think, that deals with the printer issue. Even if it is only to set a page break, check what row it is in, write that to a custom document property for safe keeping, and remove the pagebreaks again. Probably another job for the before_Print routine, as you'd want to capture it if they changed printers during their session.

Food for thought...

Murphy will have it though, that as soon as you mock all that up, someone will come along and share a way to do it easily. ;)interestingly, the more difficult aspect of this is removing the original pagebreaks. For this particular case, there are a mix of autogenerated pagebreaks and manually inserted pagebreaks; some 37 in total. I can remove the manually generated ones manually, but not using VBA. I have tried several methods to sequence through the pagebreaks, but none seem to work. For example:
Dim HPB As HPageBreak

For Each HPB In ActiveSheet.HPageBreaks
HPB.Delete
Next HPB fails with the first pagebreak.
Similarly
Dim I As Long

For I = ActiveSheet.HPageBreaks.Count To 1 Step -1
ActiveSheet.HPageBreaks(I).Delete
Next I fails the first time through the loop.

Then I examined the pagebreak object more carefully and discovered that you can not delete autogenerated pagebreaks (type = -4105) , but you can delete manually inserted pagebreaks (type = -4135). Live and Learn. I suspect that I will be able to move the code back so that it is called by the Before_Print procedure.

Ken Puls
09-07-2006, 02:03 PM
I think that this could be worthy of a KB entry when you're done with it. :)

My feeling on merged cells is that they are required, but most people use them when there is no need. "Center Across Selection" works just as well for most cases. Granted their are a few situations where merging may become necessary, but they are very few. Based on the things it does to normal coding... they're evil and I avoid them at all costs. Just my 2 cents. ;)

mdmackillop
09-08-2006, 01:23 AM
To remove all your manual pagebreaks (including vertical ones!)
ActiveSheet.ResetAllPageBreaks

shades
09-08-2006, 02:48 PM
My feeling on merged cells is that they are required, but most people use them when there is no need. "Center Across Selection" works just as well for most cases. Granted their are a few situations where merging may become necessary, but they are very few. Based on the things it does to normal coding... they're evil and I avoid them at all costs. Just my 2 cents. ;)

I agree about doing away with merged cells. Not only do they interfere with VBA, but some Excel capabilities do not work with merged cells.

Usually when I am training someone I comment:


=============================
You can use merged cells under one condition: Someone has a gun pointed at your head and will shoot if you don't use merged cell. You can begin to think about merged cells. But make sure the pistol is loaded. Then, and only then use merged cells.
========================

But at least, I don't exaggerate the problems with merged cells. :rofl:

:hi:

Ken Puls
09-08-2006, 03:22 PM
ROFL!

Love it, Rich! :rotlaugh:

MWE
09-12-2006, 10:27 AM
To remove all your manual pagebreaks (including vertical ones!)
ActiveSheet.ResetAllPageBreaks
Thanks; this could be useful. Unfortunately, I only want to reset or delete the horizontal page breaks. I have not found a .ResetHPageBreaks

MWE
09-12-2006, 10:32 AM
I agree about doing away with merged cells. Not only do they interfere with VBA, but some Excel capabilities do not work with merged cells.

Usually when I am training someone I comment:


=============================
You can use merged cells under one condition: Someone has a gun pointed at your head and will shoot if you don't use merged cell. You can begin to think about merged cells. But make sure the pistol is loaded. Then, and only then use merged cells.
========================

But at least, I don't exaggerate the problems with merged cells. :rofl:

:hi:I agree that merged cells can be a pain and there are some things that do not work as expected with merged cells. That said, I still contend that there are times when merged cells are very useful. I have written a few macros to "do things" with merged cells that has made them a bit less painful.

sherryjoo
05-25-2011, 11:52 AM
Is it too late to ask you to post the code that would do this? I'm having the same issue and can't figure out what I'm doing. Thank you so much!