Filtered cells return after saving



Excel 2007... After removing columns from a document Im left with rows that
have blanks in some of the fields. I click on the column that has blanks, go
to data/ filter to uncheck blanks and then do a sort on the table to
alphabetize the list thats left. I save the document thinking it all looks
grteat. But when I reopen the document, the filter is not saved. the rows
that have blank fields are back. Is it because there are other cells in the
row that are not blank? Im confused. What good is filter? I need to filter
the list to remove all rows with blanks in a particular column. None of the
other data in the rest of the row that contains a blank matters. Why isnt the
filter being saved? Any answers please?????

Thanks, Susan




Hello again, Susan,

as I advised you before, apply the filter to the appropriate column,
and select Blanks from the bottom of the filter pull-down. This will
show only those rows which have blanks in that column, which are the
ones you want to get rid of.

Then you should highlight all of the rows that are visible, i.e. click
on the row identifier for the first visible row, hold down SHIFT, and
click on the row identifier of the last visible row, then release
SHIFT. These are the rows you want to delete, so while they are
highlighted click on Edit | Delete Row - now you should see nothing
below your headers, and you will need to click All from the filter
pull-down in order to see the remaining rows. You can then sort these
if you wish.

NOTE that you have to delete the rows with blanks in them - it is not
sufficient to just hide them by selecting Non-blanks from the filter
pull-down, which is what I think you are doing.

Now you should click the Save icon - I understand from your earlier
post that you have started with a .csv file, and so at this stage
Excel will warn you that you will lose some features if you save back
as a .csv. You can go ahead with the save anyway, but if you want to
avoid this message, then click on File | Save As and choose Excel
workbook in the File Type box, give the file a suitable name (or keep
it the same if you wish), then click the Save button. In this case
your original file remains unchanged, so next time you should open
the .xls (or .xlsx) file you have just saved, and that should have the
rows with blank cells removed from it.

You could then use File | Save As to save this as a .csv file -
select .csv in the File Type box and give the file a suitable name.

Hope this helps.



Hell again Pete...

Yes, I was unchecking just the blanks on the pull down.

But a strange thing is going on doing it like you said. Say the list has
60,000 rows, and there are blanks in the first column, but none in the second
column. When I do as you stated and deselect all and check only blanks, you
said I will be left with only rows that have that blank cell in the first
column. But thats not what Im experiencing. It is still showing the 60,000
rows, only now they are all blank in the first column and still have a filled
cell in the secoind column. So I highlight all 60,000 rows and right click
and delete rows (takes a long time waiting on Vista or Excel to process that)
and then go back to the filter and select all...... and it works, but I
thought it strange that all 60,000 rows were still there when selecting just
blanks. Am I still doing something wrong?

I thank you... your method did work other than its showing all 60,000 rows
that I have to delete. Its why I didnt follow your instructions the first
time... it didnt look right... it should have only showed about 8,000 rows,
not all 60,000 or so I thought (so I thought I misunderstood you and was just
supposed to uncheck blanks in the filter pulldown because at least that
appeared to work).

Anyway... thought I would relate this because it seems like a glitch... the
novice Excel user thanks you (Im a marketing designer so I use Publisher and
another graphic program, so Excel is a little foreign to me)..... and thanks
for the advice on how to save the csv file.


Roger Govier

Hi Suzie

When you are selecting Blanks, it will include the rows where there is data
in other columns in those rows, as well as the remainder of the sheet which
have not yet been used, as they will be blank also.

You might be better to Sort the whole block of your data, by the column
which contains blanks.
This will cause all of the rows with blanks to go to the end of the list.
Now delete all rows at the end of the list, where there is data in other
columns (but not the one you sorted on).
Then re-sort the whole table by whichever column you require for your sorted





when you apply a filter the records not selected by the filter are
still there - they are just not displayed. The row indicator will turn
to blue to indicate that a filter is active, and if you look carefully
you will see that some rows are missing in the sequence (i.e. those
that do not match the filter criteria). As you are displaying 52,000
out of 60,000 rows, you could easily conclude that all the rows are
being displayed, but I assure you that they are not.

Deleting 52,000 rows will take some time, as you have discovered. If I
had known that you had so many to delete I would have suggested an
alternative, but I won't confuse you more with that now. Filters take
a bit of getting used to, but maybe you should get some practise on
smaller files.

Anyway, you got it to work, and I'm glad to hear that.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question