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

elisep

New Member
Joined
Nov 17, 2005
Messages
23
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,488
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 17, 2005
Messages
23
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,488
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 17, 2005
Messages
23

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,488
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 17, 2005
Messages
23
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,458
Messages
5,572,249
Members
412,451
Latest member
newbie22922792
Top