Problem with autofilters

  • Thread starter Thread starter Kathe.Aguirre
  • Start date Start date
K

Kathe.Aguirre

The new Excel 2007 is very good, all the possibilities to work with
big databases, the flexibility in pivot tables and filters is awesome.
However I am having a very big problem. When I am working with
autofilters I can't choose a category and fill the row of other column
or the same because the hidden values are overwritten. The same
happens dropping rows or copying. This is very uncomfortable and
didn't happened with the last Excel. Thanks for your help.
 
Hi Kathe

Could you explain a little more about the problem you are experiencing.
I find the behaviour in XL2007 with Autofilter, to be the same as XL2003
provided your selection remains within the bounds of the filtered area.
 
Thank you for your answer. The problem is that for example I have a
list with 1, 2 and 3 and I select with an autofilter the 3. If I want
to fill all the rows for the selection in other columns, Excel
overwrites also the rows with 1 and 2 that are hidden in selection. If
I select from a list a number with autofilter and I want to copy this
list, the result when I paste is the original database, without the
filter. If I want to drop the rows for a selection by autofilter,
Excel also drops the other rows that are hidden. To avoid this I have
always to sort the information in the column that I am working with
the autofilter. I hope to be clearer, I am learning English.
Best,
Katherine
 
Hi Katherine

Your English is perfect.

I have been able to reproduce what you say with Autofilter.
I did exactly the same as you, selected on 3 as my items number, used the
fill handle to fill down all values in the next column to the filtered
column with the same value that appeared in the first filtered row, and, as
you say, it copied not only to the filtered rows, but also to the unfiltered
rows holding values of 1 and 2, that existed within the range of the first
row with 3 and the last row with 3.

I will report this to MS as a bug.

In the meanwhile, there is a workaround.

Remove your filter totally.
Place your cursor in the header row>Insert tab>Table>tick my data has
headers.
You will now have an Excel Table, with Autofilter automatically applied to
the header row.
Filter on 3 as before, fill down as required and it will behave correctly.

Tables in XL2007 has very many nice features, and I tend to use them rather
than Autofilter most of the time, which is probably why I hadn't come across
your problem.
 
Dear Roger,
Thank you very much for your help! Please keep me in touch of this.
Regards,
Katherine
 
hi all ! (just as a follow-up to Kathe who posted the same question in the spanish excel n-g)

Kathe mentioned that she "... want to fill all the rows for the selection in other columns ..."
- and I guess she uses the fill-handle "to the right" ("... in other columns ...")
- Roger, you commented that you used "fill-down"

FWIW, I produced a list with only numbers (1 to 3) and used simple autofilter (i.e. by 3) (NOT the list feature)
- filling to the right, what I get is only visible rows were filled in the next columns (to the right)
- copying the filtered range to other place, copies only the visible cells/rows
- deleting the filtered range, and only visible cells were deleted

- also, filling-down copies only the last filtered row -?-

(just in case) I used xl-2007 spanish version
and I get exactly the ssme behavior as in previous versions (spanish all)
(I'm not sure this could be considered a bug -yet ?-)

I might miss something at some point in this thread

hth,
hector.
 
Back
Top