Can't get autofilter applied to every row

  • Thread starter Thread starter SSpade19
  • Start date Start date
S

SSpade19

I have a filter problem using excel 2000 with sp3 where the filter i
not applied to every row on my spreadsheet. It only filters on th
first few rows and keeps displaying all the data below that. Thi
happens usually after I copy data from another spreadsheet. For som
strange reason the first few rows of the copied data gets filtere
while no filter is applied to the remaining data. Another thing
noticed is that whenever the filter is only applied to the first fe
rows, the row number of those rows is also highlighted in blue
Something tells me that there must be an option in excel to extend th
filter to all rows in my spreadsheet
 
Try using a defined name for the range to filter and a macro to do the
filtering
insert>name>define
filterrange
=offset($a$2,0,0,counta($a:$a),20)
 
thanks Don

but i think what i'm trying to do should be much simpler. I just us
the autofilter option. Basically I selected one row that contains al
the columns that will serve as my filter criteria, then hi
data/autofilter. The filter should then work on the entire spreadsheet
but what i find is that especially after copy/pasting data from othe
sheets, the filter will only work on the first rows
 
If you don't have more than 1000 rows try adding any value such as xxxxxxxx
to the LAST row.
 
Sometimes, if you let excel guess at the range to autofilter, it doesn't guess
what you want.

Try removing the autofilter, then selecting the whole range you want filtered.
Then reapply Data|Filter|autofilter.
 
thanks,

that worked. Turns out autofilter will only filter down to the las
non-empty row. I didn't know that i coud select an entire block of dat
and autofilter will view the first row as the column names
 
Excel likes to use blank columns and rows as "end of ranges". Because of this,
I like to select my range before I do autofilters, sorts, pivottables--anything
that excel may not guess correctly.
 
That would have different problems--the data had gaps and you relied on
=CountA().

And when I'm doing it manually, I can do:
ctrl-End (to get to the bottomright cell)
ctrl-shift-home (to get topleft cell--or close to it with freeze panes on)
(a couple of shift uparrows/shift left arrows and I can get past that frozen
pane)

And then click on the autofilter icon (dragged to my favorite toolbar).

but I get your point that the dynamic range name can eliminate many problems
when you know (and can depend on) your data being there.

Don said:
Sort of like what I said to start with. Filter on a defined range name.
Click to expand...
 
Back
Top