Filtering in XL2003 -- where did filter by blanks go, pls?

[MitchellWMA]

There were two features under the filtering pulldowns that we could
assign to columns that would really make life easier. We could filter
by non-blanks and my blanks. Now when I click on the pulldown filter
arrow neither of these appear and nothing I've tried under "Custom"
filtering works.

How do we do this now, filter by non-blank rows so that all empty rows
get hidden temporarily, i.e., before a sheet print?

Thank you! D

 

[Dave Peterson]

If you don't see those options in the autofilter dropdown, then you don't have
any cells in that column that are blank. (If there are blanks, then you'll see
the nonblanks option.)

When you filter by any value, you'll notice that the row numbers change colors.
This indicates that the range is filtered and it also helps find the actual
range that is filtered.

My guess is that your range isn't extended as far as you want.

I'd remove the autofilter, select the complete range (don't let excel guess) and
then reapply the filter.

 

[MitchellWMA]

If you don't see those options in the autofilter dropdown, then you don'thave
any cells in that column that are blank.  (If there are blanks, then you'll see
the nonblanks option.)

When you filter by any value, you'll notice that the row numbers change colors.
This indicates that the range is filtered and it also helps find the actual
range that is filtered.

My guess is that your range isn't extended as far as you want.

I'd remove the autofilter, select the complete range (don't let excel guess) and
then reapply the filter.

Dave, hi! Thanks.

I guess this is one of those things that in trying to be helpful,
Excel has gone "backwards" since it is something that XL2K does
automatically. No spreadsheet that I've converted to 2003 or made in
2003 has this option in the autofilter, which is the feature that I
put on top of the columns to filter the rows by. I don't name these
ranges, not here, because the rows keep getting bigger and bigger as
information is added, so the problem may lie in the fact the XL2003
requires me to do an extra step XL2K never did, so it will be good to
know.

The rows are, indeed, blank. They don't even, in this case, have
formulas in them. They are conditionally formatted, yes, for row
colouring purposes, but that's it.

It's a common feature that I have used for several years now where the
print button is attached to a macro that filters out blank rows, sorts
and then brings up the print dialogue box. In 2003, this never works
now, if memory serves.

Is there a link to something that will help teach me to do the same in
2003 as in XL2K, by any chance? It's a feature I'm sorely missing.
The workaround I'm using is to select the rows in question and print
the selection. Hopefully there is something that can help bring the
earlier, better functionality back.

Thanks! D

 

[Dave Peterson]

For what it's worth, I've never let excel guess at the autofilter range--in all
versions of excel (back to xl95???). I just don't think it's worth it. If it
guesses wrong, I have more work to do. If it guesses incorrectly, I may not
notice it until I've relied on the results of the filter.

I'm not sure how naming of ranges fits in. I don't name the range I need when I
apply data|filter. And if I add more rows (with data!) to the already filtered
range, excel will include those rows the next time I use the dropdown arrow.

If you really want to include those unused rows at the bottom of your range,
maybe you can try this:

Remove the autofilter.
Put a value (just temporary) in one of the fields in the last row that you want
in your autofilter range.
Select the range.
Apply data|filter|autofilter

and clear out that value in the temporary cell.

If you're selecting the entire columns, then I bet you're just noticing that
excel stops guessing at the last used cell. By adding that temporary value,
it's extending this usedrange.

Personally, I don't like to have those empty rows at the bottom of my range
included in the autofilter range.


MitchellWMA wrote:

 

[MitchellWMA]

Okay, I see now what I've been doing and what you're describing. I
never "named the range" as you call it, what I know by selecting a
range of cells before applying the filter under DATA - FILTER -
AUTOFILTER. I always have selected the pertinent columns in the
header row only, since sometimes the placement the filter pulldown
arrows is very specific. I don't apply them to every single column.
I foresee a problem with this changed functionality in future
instances but in this case, it worked and "Blanks" and "NonBlanks" do
appear. After all these years, I guess I got lucky as I've never had
a problem with O2K. Just luck, I guess.

I selected all the rows, empty or not and then applied the filter
automatically. This time, filter placement was fine and now I have
option to filter out blank rows before printing.

I understand when you say you wouldn't select empty rows as well, but
my spreadsheets are built in a certain manner and adding/deleting
rows, etc., is done by macros. I don't know how the selected range
will work if I didn't include the empty rows as per normal, even if I
never "named" (selected) the range before. I'm going to leave it like
that for now as I have my usu. functionality back. I will test this
on a dummy XL file later, doing it more in the way you say.

At any rate, even though it took a few more mouse clicks, I at least
have this functionality back. Thank you!!

Cheers. D

 

[Dave Peterson]

I don't like having completely empty rows (or columns) in my range to be
filtered, either. If I add a row directly under the existing filtered range,
the added row is included in the filtered range.

And I still don't think that this is a change from any previous version--but
that's not too important <vbg>.