Controlling print area of sheets with variable-width content

Hi, I hope this is simple, but a couple of hours of web searches have been less than completely useful.

I am printing family tree diagrams. I have determined by trial and error (MANY errors) that I can build no more than three generations on a sheet before the format forces creation of a second worksheet page that contains the overflow. I am trying to make this application not do that. I want to be able to put a page number at the bottom of whatever is printed and NOT print an extra sheet because of the overflow data, which is why the format I described. So I build my pages in VBA with the three-generation format.

My current problem started when my sheets ended up showing thin but not invisible grid lines, I'm guessing the smallest possible line thickness and 50% gray(?) because that is the color used for graph gridlines by default - but the actual color is immaterial. It is more that the color is not transparent or white.

So in an attempt to prevent the gridlines from showing up, I went through and told Excel to set all of the gridlines in a given range ("A1:J34" in this case) to be vbWhite. That range is an APPROXIMATE guess at the area on the sheet that would fit on one page. All of the cells.Interior.Color were set to vbWhite as well, and the .Value for the entire range was set to "" - the idea being that I would have a totally blank canvas. Then I went back and filled in the stuff I wanted. The "filling in" part IS working.

The problem came up that I have to use AutoFill on the columns that have names in them. Some names are simple, like "Susan Smith" but others are much longer, like "Margarita Esperanza Espinoza" (both fictitious names). So what happens is that the page breaks on each sheet occur based on the lengths of the longest names in the boxes in the A, C, and E columns, and that varies from one sheet to the next. I actually will never have anything to print beyond range "A1:G32" on any sheet but I wanted to get rid of the gridlines in the unused areas. Unfortunately, the fact that I erased that range appears to make Excel think I have something there and that triggers an extra page for the "overflow" even though it is totally blank. Sometimes it appears that it is the last erased column ("J") that overflows. Sometimes "K" (which is NOT blanked out) slides in on the sheet and I get the gridlines anyway, because maybe on that page only two generations were left to be displayed, so the other columns, when I apply Autofit, really shrink down. Between blank pages and the occasional gridlines that appear on the edge of each sheet, I find myself having to manually resize every flippin' page. I was automating this process in the first place so as to NOT have to go back and touch it up because that is time-consuming.

Either of two answers would work for me. I need the answer in VBA terms because I'm automating the family tree generator for members of my family to be able to ask for a tree specific to each person. (If it works right and I don't need to go back and manually retouch the pages, the code takes 6.3 to 8.3 seconds to run for an entire family tree for one person.)

Option A: Tell Excel to turn off ALL of its gridlines in a way that will not show up when printing, but in a way that doesn't "touch" the cells so that Excel thinks the cells were "busy" and wants to print them anyway.

Option B: On any given page, tell Excel not to print anything beyond range "A1:G32" - no overflow pages desired or required. I.e. "A1:G32" is THE ONLY print area for the given sheet, no matter how many cells I actually blank out.

Sorry if this got a bit long-winded, but that is the way I am. I wanted to give a good explanation of what is going wrong.

So that folks could see what I'm building, I attempted to upload an image file using the image tool. However, for some reason the little Image icon pop-up will not allow me to browse for the image. I also tried to use the Attachments icon but that failed, too. I don't know how to show you what I am trying.

Keebellah, the ActiveWindow.DisplayGridLines = False did EXACTLY what I needed. Thanks!

The reason that I had a larger range originally being referenced was to kill the gridlines in a range that COULD have been printed - but setting the print area to the "right" settings in combination with the .DisplayGridLines trick - did what I wanted. Exactly the desired effect, and it prints perfectly with no blank pages to an "overflow" from the sheet's intended area.

As to attempting to upload something, when I click on the Image icon in the 2nd line of the reply window's icon bar, I get a pop-up dialog box and then my browser asks me if I want to allow Flash to run. I click "Allow" and at that point, the dialog box is totally non-responsive.

When I went to Advanced, on the top line of the three-line icon bar, there is an "Attachments" option. When I click on that, I get a very long, skinny box, looks like a simple text box, that MIGHT fit 4-point text (barely) but it doesn't seem to want to play with me any more than the image dialog did.

For those curious about what was eventually done, I had to dig a bit more to see what was in which collection, but this works to turn off gridlines on a worksheet. Note that if you apply Borders to selected cells, this DOES NOT interfere.

Please Login or Register  to view this content.

Since the problem has been resolved, I'm going to mark this as "Solved"

Yes, I knew the gridlines aren't borders. My point was that if you WANTED borders, they worked just fine.

My problem, Keebellah, is that like many people new to an app, I don't always know the right words for what I seek. However, I may be a bit different in that with Access, I am intimately familiar with VBA and object/property/method usage. I just don't know the names of the Excel objects, properties, and methods because most of what I do, I do in Access. As a former Access MVP and a 40-year veteran of programming for a living, I know how to do behind-the-scenes things pretty well, including how to research an issue, witness how long it took me after your hint to find the working solution. BUT, as I said, sometimes I don't know the right question because to me, Excel is an alien environment. Case in point: Once you pointed me in the right direction, I was able to ask the right question to find that little snippet even though it wasn't exactly what you said.

As a secondary issue, I can't open the PDF that you posted. Thanks for the effort, but nothing happens when I click on the icon. I would have expected Kaspersky to ask me if I wanted to really do that (for which I would have said YES), but I get dead silence. I'm on Firefox (latest and greatest or no more than 1 version back from that, not sure which) and Win 7/64 bit. Windows is ALSO updated to no more than one set of patches back, and I THINK I'm up to date on them because I haven't been getting "patch me" notices. And we ALL know how persistent Windows can be about THOSE notices.