cell population w/ data list applied + cell borders on?

[elisep]

When I go to print one of the worksheets in my workbook Excel is detecting 1,726 pages (through the bottom of the sheet, i'm assuming). Now, the cell borders have been manually turned on (instead of opting to "print gridlines"), because I need to have one of my column borders hidden (turning 2 of the columns into a visual single column, but 2 for sort purposes). Additionally, I have data validation in a couple of the columns on the sheet. Otherwise, I can think of nothing that would be forcing the cell population all the way down the sheet (there are 2 pages worth of data, populated).

I've tried selecting all rows to the bottom of the sheet and clearing contents. I've played with different worksheets in the same workbook and don't seem to be having the same issue in other sheets. If I turn off the cell lines, only two pages of data are detected. When I turn those lines back on I've got 1,000+ again.

Any thoughts on the soure of this problem and how to overcome it?

Thank you!

elise

 

[Peter_SSs]

Now, the cell borders have been manually turned on (instead of opting to "print gridlines"),

Which cell borders? The whole sheet? How have you 'turned on' the cell borders? If you just apply borders to the data area, do you still get the same problem?

 

[elisep]

The whole sheet. Clicking the top left corner to select the entire sheet and then selecting that all perimeter borders show. If I apply borders to just the data area I don't get the same problem, but this document is being handed off to someone else in 5 days, and she's not very comfortable with Excel. And in other worksheets of the same book I can select the entire sheet draw cell borders, and there is no problem detecting the end of the populated area of the sheet.

 

[Peter_SSs]

elisep

I think that I have been able to reproduce something like you describe and am not sure why it is happening either. Try this as a suggestion.

1. With the cell borders 'on', select all columns to the right of the data area.
2. Edit|Clear|All (this of course will remove the borders from much of the sheet.)
3. Repeat the above for rows below the data area.
4. SAVE the sheet.
5. Do a Print Preview to see if you now just have your two pages.
6. Select the whole sheet and re-apply the cell borders.
7. Check Print Preview again.

This worked for me. Let us know how it goes.

 

[elisep]

thanks for your reply. i just tried that and didn't have any luck. i've been going back and forth with this problem all day now... i selected the data and created new worksheets and pasted in the data, and that worked fine for a while, but as soon as i went to format the columns down to the bottom of the sheet (in anticipation of future data) it started defaulting to a print of the entire sheet again.

this is really frustrating! anybody have any ideas?

thanks!
elise

 

[Peter_SSs]

i selected the data and created new worksheets and pasted in the data

1. How did you select the data? and how big is your data area at the moment? Is there any limit on how big the data area might get?

2. If you start with a fresh sheet can you reproduce the problem WITHOUT using your current data?

I don't have any definite ideas on the problem, but the answers to these might trigger an idea somewhere.

Another option would be to leave off all the borders and have some VBA code apply them to the data area only at the time of printing using the 'Before Print' event.

 

[elisep]

I had all sorts of occurences of this problem yesterday. it seemed to relate somehow to setting the formatting on the worksheets down to the end of the columns. Ultimately, my supervisor acknowledged that the cell borders weren't worth the trouble they were causing, all for one invisible column border, and so we've switched back to the 'view gridlines' option in page setup, which now seems heavenly problem-free!

Thanks for your interest, and efforts!

Elise