Autofilter problem

[Big_Belly_Bob]

Ok has anyone come across this problem before?

You're using an autofilter on a large amount of data in excel say 4000 lines or something, and you pick out just half a dozen lines that have something you don't want in i.e. lines with 0 or with text that doesn't match what's on the other lines. Then you delete the lines.

Normaly I'd exspect this to just delete the few rows that you have filtered on leaving the rest of the 4000 lines so that when you unfilter you are left with just the data that you want. However very occaisional I find that it deletes all the lines in between as well so that when I unfilter I'm left with only a couple of hunderd lines.

For me it doesn't happen that often but I have a friend who says it happens to here all the time and she's actualy now paranoid about it. I can't see that she's doing anything different to me and I can't see why it occaisionaly does it to me. I've seen this in Excell 2007 and 2003 has anyone else seen it? and also if you have do you know what's causing it? is it user error? or something to do with the spreadsheet?

 

[olieph]

Hi there,

just highlight the filtered data and hit the "select the visible cells" icon, then delete it. it will only delete the filtered data.
to have a "select the visible cells" icon shortcut on your toolbar:
go to view-toolbars-customize-commands-edit, find the "select the visible cells" and drag it to your toolbar.

i hope it helps.

 

[njimack]

Rather than simply selecting the rows and deleting them, select them then use Edit>Go To>Special Cells>Visible Cells only, and delete those rows.

 

[Gettingbetter]

The problem you/your friend is having is because of named ranges (kind of anyway)

Excel can only make so many named ranges before it reaches its full memory capasity (I found this out the hard way once) I think its around the 3000 mark but I could be wrong.

When using autofilter, Excel uses the same technology to create the sections of visible cells.

So if you have 60000 lines of data but only 2 sections of visible cells (lets say true/false), Excel will only have to make 2 named ranges (not a problem).

However if you had 20000 rows but your filter meant that it had loads of sections (something like list everything containing a 0) then if Excel cant manage all the named ranges it simply cancels that part (without telling you unfortunatly) and then you are left with the situation where you will delete rows inbetween etc..

Sorry I realize reading the above my explanation is pants..

Basically to avoid this problem I very rarely/never use autofilter for deleting rows (I usually sort instead).

If I must use autofilter, before I delete, I goto;
Edit, Goto, choose 'Special', then select 'visible cells only'

If Excel can not create the amount of named ranges when you click delete after that it will tell you.

Cheers
GB

 

[Big_Belly_Bob]

Works but the problem with that is that it deletes everything visable eg including column headings and anything else in the spreadsheet. also since I'm still not sure what it is that's causing the problem I don't know if that will definitly work or not, like I say doesn't seem to happen to me very often usualy selecting lines and deleting works fine :S

Any more thoughts?

 

[Big_Belly_Bob]

The problem you/your friend is having is because of named ranges (kind of anyway)

Excel can only make so many named ranges before it reaches its full memory capasity (I found this out the hard way once) I think its around the 3000 mark but I could be wrong.

When using autofilter, Excel uses the same technology to create the sections of visible cells.

So if you have 60000 lines of data but only 2 sections of visible cells (lets say true/false), Excel will only have to make 2 named ranges (not a problem).

However if you had 20000 rows but your filter meant that it had loads of sections (something like list everything containing a 0) then if Excel cant manage all the named ranges it simply cancels that part (without telling you unfortunatly) and then you are left with the situation where you will delete rows inbetween etc..

Sorry I realize reading the above my explanation is pants..

Basically to avoid this problem I very rarely/never use autofilter for deleting rows (I usually sort instead).

If I must use autofilter, before I delete, I goto;
Edit, Goto, choose 'Special', then select 'visible cells only'

If Excel can not create the amount of named ranges when you click delete after that it will tell you.

Cheers
GB

Ah Cheers!! that exsplains it, altho using select all cells is still a bit of a pig but at least I know what the problem in Cheers GB

 

[Big_Belly_Bob]

Actualy just tryed this in 2007 and it does tell you but it then it does it anyway and deletes all the lines in between but suspect it's a simmlar problem and sorting first may help