I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

Hi Larry -

1. Make sure the blank rows below your database are actually empty. If
they contain spaces, Excel will print them as blank rows.

A good way to clear them out is to select any cell in the row just below
your last row of data, hold down the Shift key, tap End, tap the down arrow,
then let up on the shift key. Then choose |Edit|Delete...|Entire Row.

2. Also, choose |File|Page Setup...|. Go to the 'Sheet' Tab and check for
an entry in the 'Print area' reference box. If there is a range listed there
and it includes blank rows below your database, those rows will print
resulting in your symptom.

I'll be offline immediately after this post, but will check back in about 10
hrs. Good luck.
--
Jay


"Larry" wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

If you can pick out a column that is only empty when the row isn't used, maybe
you can just apply Data|Filter|autofilter to that column.

Then filter to show the nonblanks and print that.

Larry wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

--

Dave Peterson

I have created a named range to include the columns I want all the way to row
65536. I have used that range to create an auto filter. When I filter for
non blanks It hides all the rows with blanks in that column all the way up to
row 51127. The blank rows from 51128 through 65536 are not filtered out. Is
there a limit to the number of rows auto filter works on.

"Dave Peterson" wrote:

If you can pick out a column that is only empty when the row isn't used, maybe
you can just apply Data|Filter|autofilter to that column.

Then filter to show the nonblanks and print that.

Larry wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

--

Dave Peterson

Hi Larry -

I'm not aware of any record limitation on the autofilter. Ive never
experienced the problem you describe when dealing with large numbers of
records. It's been a pretty stable and predictable tool.

Without seeing the file, I dont know if I have an answer for your latest
post. If you would like, you can email it to and
Ill take a look. I cant speak for Dave, but Im guessing that he would be
interested, too, if he accepts emailed files (some people prefer not to due
to virus concerns).

I keep hitting dead ends when analyzing your problem, hence the request to
see the file. As Dave suggested earlier, filtering for non-blanks should do
the trick, so I cant explain the behavior.

Also, let us know what version of excel you are using.

--
Jay


"Larry" wrote:

I have created a named range to include the columns I want all the way to row
65536. I have used that range to create an auto filter. When I filter for
non blanks It hides all the rows with blanks in that column all the way up to
row 51127. The blank rows from 51128 through 65536 are not filtered out. Is
there a limit to the number of rows auto filter works on.

"Dave Peterson" wrote:

If you can pick out a column that is only empty when the row isn't used, maybe
you can just apply Data|Filter|autofilter to that column.

Then filter to show the nonblanks and print that.

Larry wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

--

Dave Peterson

I've never seen a limit on the number of rows that can be filtered.

But I have seen examples where the range that was filtered wasn't what the user
wanted--and the filtered range was either too large or too small.

I'd guess that your defined name isn't pointing to what you expected. (Maybe
the range got resized after you defined it???)

If you hit F5, then type in that named range, does your range get selected
correctly?

You may want to share how you created the name, what it's currently pointing to
(the address) and how you applied the data|Filter|autofilter.

If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make working with names much easier.



Larry wrote:

I have created a named range to include the columns I want all the way to row
65536. I have used that range to create an auto filter. When I filter for
non blanks It hides all the rows with blanks in that column all the way up to
row 51127. The blank rows from 51128 through 65536 are not filtered out. Is
there a limit to the number of rows auto filter works on.

"Dave Peterson" wrote:

If you can pick out a column that is only empty when the row isn't used, maybe
you can just apply Data|Filter|autofilter to that column.

Then filter to show the nonblanks and print that.

Larry wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

--

Dave Peterson


--

Dave Peterson

I don't open other people's files.

Jay wrote:

Hi Larry -

I'm not aware of any record limitation on the autofilter. Ive never
experienced the problem you describe when dealing with large numbers of
records. It's been a pretty stable and predictable tool.

Without seeing the file, I dont know if I have an answer for your latest
post. If you would like, you can email it to and
Ill take a look. I cant speak for Dave, but Im guessing that he would be
interested, too, if he accepts emailed files (some people prefer not to due
to virus concerns).

I keep hitting dead ends when analyzing your problem, hence the request to
see the file. As Dave suggested earlier, filtering for non-blanks should do
the trick, so I cant explain the behavior.

Also, let us know what version of excel you are using.

--
Jay

"Larry" wrote:

I have created a named range to include the columns I want all the way to row
65536. I have used that range to create an auto filter. When I filter for
non blanks It hides all the rows with blanks in that column all the way up to
row 51127. The blank rows from 51128 through 65536 are not filtered out. Is
there a limit to the number of rows auto filter works on.

"Dave Peterson" wrote:

If you can pick out a column that is only empty when the row isn't used, maybe
you can just apply Data|Filter|autofilter to that column.

Then filter to show the nonblanks and print that.

Larry wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

--

Dave Peterson


--

Dave Peterson

I have gone to insert/name/define and defined the range to $B:$645536. When I
use the scroll bar to to the right to scroll to the bottom of the page it
only scrolls down to approx 51000. I must have a setting somewhere that is
limiting that but I'm not sure where. When I set my print range to row 51000
I get no blank pages. If I set it to 645536 I get 200 plus blank pages.

"Dave Peterson" wrote:

I've never seen a limit on the number of rows that can be filtered.

But I have seen examples where the range that was filtered wasn't what the user
wanted--and the filtered range was either too large or too small.

I'd guess that your defined name isn't pointing to what you expected. (Maybe
the range got resized after you defined it???)

If you hit F5, then type in that named range, does your range get selected
correctly?

You may want to share how you created the name, what it's currently pointing to
(the address) and how you applied the data|Filter|autofilter.

If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make working with names much easier.



Larry wrote:

I have created a named range to include the columns I want all the way to row
65536. I have used that range to create an auto filter. When I filter for
non blanks It hides all the rows with blanks in that column all the way up to
row 51127. The blank rows from 51128 through 65536 are not filtered out. Is
there a limit to the number of rows auto filter works on.

"Dave Peterson" wrote:

If you can pick out a column that is only empty when the row isn't used, maybe
you can just apply Data|Filter|autofilter to that column.

Then filter to show the nonblanks and print that.

Larry wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

--

Dave Peterson


--

Dave Peterson

First, The address you gave $B:$645536 gives an error for me. I'm guessing that
you meant $2:$65536.

If you keep clicking on the arrow at the bottom of the vertical scrollbar, can
you see what else is selected. But the scrollbar will only go as far as the
last used cell.

I selected the range via Edit|Goto (or F5) and then A2 was the activecell. I
could hit shift-tab and go to the bottom right most cell. In my case, IV65536
was selected.

=========
If you don't have any data in rows 51001 to 65536, but still tell excel that you
want to print it, you're going to end up with lots of blank sheets.

I still don't understand why you're using a range name to apply the
autofilter--or how you do it. Excel is pretty smart--if your data is
contiguous, the data|filter|autofilter should react to any additional rows you
add. (It may have trouble if you leave empty rows (or columns) in that range.)

In fact, if you make sure that excel's last used cell is what you expect it to
be, you could even use that to print. Excel will only print what it thinks is
used if you don't change the print range.

A warning. If your data is in rows 1:10 and you put something in row 44444 and
deleted it later, then excel still sees that lastused cell in row 44444.

Debra Dalgleish shares some tips on how to reset this last used cell:
http://contextures.com/xlfaqApp.html#Unused


Larry wrote:

I have gone to insert/name/define and defined the range to $B:$645536. When I
use the scroll bar to to the right to scroll to the bottom of the page it
only scrolls down to approx 51000. I must have a setting somewhere that is
limiting that but I'm not sure where. When I set my print range to row 51000
I get no blank pages. If I set it to 645536 I get 200 plus blank pages.

"Dave Peterson" wrote:

I've never seen a limit on the number of rows that can be filtered.

But I have seen examples where the range that was filtered wasn't what the user
wanted--and the filtered range was either too large or too small.

I'd guess that your defined name isn't pointing to what you expected. (Maybe
the range got resized after you defined it???)

If you hit F5, then type in that named range, does your range get selected
correctly?

You may want to share how you created the name, what it's currently pointing to
(the address) and how you applied the data|Filter|autofilter.

If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make working with names much easier.



Larry wrote:

I have created a named range to include the columns I want all the way to row
65536. I have used that range to create an auto filter. When I filter for
non blanks It hides all the rows with blanks in that column all the way up to
row 51127. The blank rows from 51128 through 65536 are not filtered out. Is
there a limit to the number of rows auto filter works on.

"Dave Peterson" wrote:

If you can pick out a column that is only empty when the row isn't used, maybe
you can just apply Data|Filter|autofilter to that column.

Then filter to show the nonblanks and print that.

Larry wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Larry €“

Unless you need the range named for some other reason, try eliminating the
range name completely (|Insert|Name|Define|[Delete]). The autofilter range
€˜grows automatically when you add data to the bottom of the list (at least
in Excel 2003).

--
Jay


"Larry" wrote:

I have gone to insert/name/define and defined the range to $B:$645536. When I
use the scroll bar to to the right to scroll to the bottom of the page it
only scrolls down to approx 51000. I must have a setting somewhere that is
limiting that but I'm not sure where. When I set my print range to row 51000
I get no blank pages. If I set it to 645536 I get 200 plus blank pages.

"Dave Peterson" wrote:

I've never seen a limit on the number of rows that can be filtered.

But I have seen examples where the range that was filtered wasn't what the user
wanted--and the filtered range was either too large or too small.

I'd guess that your defined name isn't pointing to what you expected. (Maybe
the range got resized after you defined it???)

If you hit F5, then type in that named range, does your range get selected
correctly?

You may want to share how you created the name, what it's currently pointing to
(the address) and how you applied the data|Filter|autofilter.

If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make working with names much easier.



Larry wrote:

I have created a named range to include the columns I want all the way to row
65536. I have used that range to create an auto filter. When I filter for
non blanks It hides all the rows with blanks in that column all the way up to
row 51127. The blank rows from 51128 through 65536 are not filtered out. Is
there a limit to the number of rows auto filter works on.

"Dave Peterson" wrote:

If you can pick out a column that is only empty when the row isn't used, maybe
you can just apply Data|Filter|autofilter to that column.

Then filter to show the nonblanks and print that.

Larry wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

--

Dave Peterson


--

Dave Peterson

Thanks Dave and Jay. By playing around with the way I selected the filter
range I was able to get it to work. By Selecting down to 65536 fora
filtering range and then making my printing range the same I do not get any
pages with nothing on them when I print. Thanks again

"Jay" wrote:

Hi Larry €“

Unless you need the range named for some other reason, try eliminating the
range name completely (|Insert|Name|Define|[Delete]). The autofilter range
€˜grows automatically when you add data to the bottom of the list (at least
in Excel 2003).

--
Jay


"Larry" wrote:

I have gone to insert/name/define and defined the range to $B:$645536. When I
use the scroll bar to to the right to scroll to the bottom of the page it
only scrolls down to approx 51000. I must have a setting somewhere that is
limiting that but I'm not sure where. When I set my print range to row 51000
I get no blank pages. If I set it to 645536 I get 200 plus blank pages.

"Dave Peterson" wrote:

I've never seen a limit on the number of rows that can be filtered.

But I have seen examples where the range that was filtered wasn't what the user
wanted--and the filtered range was either too large or too small.

I'd guess that your defined name isn't pointing to what you expected. (Maybe
the range got resized after you defined it???)

If you hit F5, then type in that named range, does your range get selected
correctly?

You may want to share how you created the name, what it's currently pointing to
(the address) and how you applied the data|Filter|autofilter.

If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make working with names much easier.



Larry wrote:

I have created a named range to include the columns I want all the way to row
65536. I have used that range to create an auto filter. When I filter for
non blanks It hides all the rows with blanks in that column all the way up to
row 51127. The blank rows from 51128 through 65536 are not filtered out. Is
there a limit to the number of rows auto filter works on.

"Dave Peterson" wrote:

If you can pick out a column that is only empty when the row isn't used, maybe
you can just apply Data|Filter|autofilter to that column.

Then filter to show the nonblanks and print that.

Larry wrote:

I have a database that continues to grow in rows. I am trying to print only
the data and not the blank lines. I have tried to make a range that includes
all of the lines to the bottom of the sheet and then filter out the blank
lines. This works to a degree but it only works to about 51000 lines and
then the blank pages start to appear again. need help

--

Dave Peterson


--

Dave Peterson