Problem using Autofilter to remove large number of rows of data

Hi,

I am enclosing a file to show the example data file i am using and how its not working.

If you run the macro "test" what is SUPPOSED to happen is all rows in the first column (except for the header row.......row 1) which do not equal "Level 2" are to be deleted. But this is NOT what happens. For some reason EVERY row is deleted and i cant figure out why?

Any ideas?

I am wondering if it has to do with the number of rows of data because this approach works fine for say 100 rows of data .............

Any ideas?

Try this code

Please Login or Register  to view this content.

Hi welchs101,
this option works for me

Please Login or Register  to view this content.

no that generates a run time error ........something about "microsoft office cannot create or use the datarange reference because its too complex"

nilem, i have not tried your code yet i was responding to the other persons code.........i will try yours now

it will be faster

Please Login or Register  to view this content.

nilem, i tried your code. it does not generate a runtime error...........but it does the same thing as my code does.........it deletes ALL the rows except the header.

very strange.............i think it may have somethign to do with the number of rows?

welchs101,
It works and does not generate any error. I can attach the code with your sample if you want to see it.

abb33...............yes please attach code to file...............

very strange..............when i ran the code you posted it generated a runtime error as mentioned.............


please attach code and i will try again.

try attached

ok....Nilem...........the second code set you posted worked.................now i have to figure out why mine did not and figure out your code.

can you guys help me out.

i have used my code of filter/delete routine so many times and this is the first time i have had an issue.

I have other programs which use this type of thing and i need to understand why my code does not work..............so i can know when i need to change it in other programs..........any ideas?

Try attached

AB33...........i ran your code using the excel file you provided and it produced a runtime error 1004 indicating Microsoft office cannot create or used the data range refernce because its too complex....................

Very strange!
I have now run the code and see the result.

Originally Posted by welchs101

...now i have to figure out why mine did not and figure out your code...

Welsh, which version of Excel are you using?
if Excel2003, then really be a problem. Different versions have different allowable number of unrelated ranges.

Nilem,
fyi: The file you posted has some issue...........the code i tested and it works............i dont know why yours works and mine doesnt. Very strange.
Why do you have the "sort" in the code? Why do you have the "usedranges" in your code? what are they doing exactly?



Ab33,
Do you think the runtime error could be the result of some setting? I am running Windows 7.............actually just converted to windows 7 about 2wks ago.

Your code works for me too. I have got the same result as mine 16239 rows left after deletion.

i am using excel 2007.

Any ideas?

As I said on my previous post, your code works. Did you run it step by step using F8 and see where is the error is shown? I think you have function in your module, this may be somehow interfering with the code.

Originally Posted by welchs101

Any ideas?

I'll try to explain (maybe AB33 will help me with my English )
When we delete the filtered rows, we use SpecialCells. Excel2003 supports 9860 (approximately, do not remember exactly) unrelated ranges for SpecialCells.
This amount in Excel2007 more, but is also finite (something about 20000).
Try this code, change the length of the array until you get an error

Please Login or Register  to view this content.

Using the sorting prior to filtration, we subsequently remove only one range and it is faster and more efficient.

I'll try to explain (maybe AB33 will help me with my English.
You are being polite and modest.
Your English language command is as good as mine. Like you, English is not my mother tongue language either.
While I am writing this thread, Nadal is (virtually )out of the Wimbledon open. It may be Andy Murray's time.

Nilem,

Can either you or AB33 explain this further "Using the sorting prior to filtration, we subsequently remove only one range and it is faster and more efficient. " Not sure i understand..........

Also, what is an "unrelated range"?

Interesting, if i MANUALLy try to do this i get an error.............ie....if i manually select all the data then show all rows which are NOT equal to "Level 2" and then try to delete these rows i get an error saying "Microsoft can not create or use the data range reference because its to complex"

Nilem's initial code took as long as mine, but when he includes a sort line, the code will not go all the way down to delete the rows since the data are sorted in order.

ab33,

Dont understand this one?

but when he includes a sort line, the code will not go all the way down to delete the rows since the data are sorted in order




Also, any idea on why my code works for you but not me?

The last row is 33397. If you were to sort the data in order, the code does not need to go as far as this last row.
You do not need a code to test this. You could use Excel's filer function and see the difference. First do it with out sorting and then do with sorting. You will see the difference in speed.
I am struggling to explain why your code does not working on your PC, it does work on mine.

AB33,
When you run my code can you send me the resulting output file?
When i run my code all the rows of data are removed except the header row..............any ideas on why this is?

Your code and the result on sheet1- the same as mine.

AB33,

Kinda confused..........you sent me an excel file that does not have "Sheet1" in it..........is this the file you wanted to send? If so this could not have been the file you used to run my macro .......right?

Also, i keep reviewing the sort code...........i kinda understand i think......but not sure............your saying that by using the sort it does not go all the way down to the last row...........But how far does it go down?


David

nilem, if you run my code does it also work for you?

Sorry!
I attached the wrong sheet.
When you filter a range, the cells are divided in to visible and non-visible. If the data are not sorted, the code still goes to the bottom though some of the rows are hidden, but if your data are sorted by order, the code will only look for the relevant rows( Let's say the first 16000 rows, not need to go to the bottom,33397).
I hope it is now clear.

ok....i found something interesting........still cant explain it but hoping someeone can.

If i add a sort prior to the filter/delete then IT works! I got the idea of doing this from Nilem's code but i think what i am doing is to "sort" prior to any autofiltering/deleting so .....i dont know why it works.

Here is the code i used and it shows the newly added sort.........

Please Login or Register  to view this content.

Anyone know why it works by adding the sort code ...........but DOES NOT work if you dont have the sort code

As Nilem mentioned, it may be something to do with the limits of specialcells. I know the limit in 2003, was 8192, but I though, the restriction has been lifted in 2007.
Can you try to split up your data in 2 half's and run each and see if this makes a difference?

hi AB33...........but i am not doing / using a special cells method......am i?

That is what Nilem wrote

"When we delete the filtered rows, we use SpecialCells. Excel2003 supports 9860 (approximately, do not remember exactly) unrelated ranges for SpecialCells.
This amount in Excel2007 more, but is also finite (something about 20000)."
If sort order works, I was guessing it something to do with the size of cells. As I said, I really do not know why your code does not work, so I can only guess. One way would be to check the size of your data, but there may be other valid reason. If you divide the data and does not work, we need to find other possible reasons.
Despite there is no specialcells in your code, there is on the auto -filter method. I do not need to use specialcells when I select filtered data, but it is a default assumption if you use auto-filter. That is my understanding.

Anyone have any thoughts on why one person would be able to run the code and another NOT? Any ideas on how to identify this? Is it a setting in excel? Or an Add-in?

Hi David,
it does not depend on the person, but from version of Excel
your original code works normally in my E2010
in other versions will probably have to perform pre-sorting to reduce the number of unrelated (non-continuous) ranges

nilem,
Thanks for the info.

Can you tell me what a non-related or non-continuous range is? I am not sure i understand what this means?

Run this code and you will understand

Please Login or Register  to view this content.

processing of continuous ranges requires fewer resources from Excel than non-continuous (discontinuous) ranges

nilem,

very cool way to describe the answer...........very cool..................


so in relation to the filter..........would a non-continuous range be something like this......would this be 4 non-continuous

ColA
X
X
Y
Y
X
X
Z
Z

Yes, something like that
Suppose you want to filter and delete rows with "X". In this case, you must remove two non-continuous ranges (lines 1,2 and 5,6)

ok....the light is starting to come on.

So for excel 2007 one can have ~ 9000 discontinuities where as in your version 2010 you have more allowable discontinuities and thus is why it runs for you.

To protect me in my code i need to add a sort to the column i am filtering on to help reduce the discontinuities. Is there a way to add the sort adn the filter in the same with-statment..........right now i am doing this (see below).......i sort prior to the autofilter.........

Please Login or Register  to view this content.

something like

Please Login or Register  to view this content.

or even so

Please Login or Register  to view this content.

another thing...........i have been searchign and searching the net to find more information about the use of autofilter and non-continuous ranges and i can find any information about it at all............can you help me?

okay David,
let's try another option without the filter

Please Login or Register  to view this content.

It's easier?

by the way 8911 of non-continuous ranges are present in your example

Please Login or Register  to view this content.

nilem,

thanks again.

Couple comments/questions:
'1-The code in the first section (prior post) this does not use autofilter and it removes all rows which equal "Level 2". Can you update the code in such a way that deletes all the rows except Level 2?

'2-The code in the second section (prior post) gives me a run time error.............is this code just supposed to indicate the number of discontinuities?


Lastly, i used the autofilter/delete method shown previously (which does not work all the time apparently) many times not knowing about this limitation. if you had to write a section of code to delete rows where values in a specific column did or did not equal a specific value..........what would you do? Is this the code in the first section or would you do something else?

thanks again nilem!

Originally Posted by welchs101

Can you update the code in such a way that deletes all the rows except Level 2?

there is a ColumnDifferences method to do this

Please Login or Register  to view this content.

This code works in the E2010, but I'm afraid it will not work properly in the E2007 due to the large number of non-contiguous ranges. (By the way, I wrote noncontinuous. Not "noncontinuous", but "non-contiguous". Sorry)

'2-The code in the second section (prior post) gives me a run time error.............is this code just supposed to indicate the number of discontinuities?

yes, the whole thing in limit for SpecialCells for E2007

if you had to write a section of code to delete rows where values in a specific column did or did not equal a specific value..........what would you do?

I think you need a code from post 6

nilem,

when you were talking about code not working in 2007 due to "limits" regarding special cells.........were you talking about the code below:

Please Login or Register  to view this content.

...one more

Please Login or Register  to view this content.

thanks Nilem.....i will review these. I will probably have a question or two about certain lines of code.........

But thank you very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

nilem, (or anyone else)


The following code is used in your example...........i dont understand what this is or why its used.........can you help me understand?

.Parent.UsedRange

Try to run the code without this line, and then press Ctrl+End to see the last cell of the range in use.
Then, run the code with this line and also press the Ctrl+End. You'll see that the the used range significantly decreased.
That is, ActiveSheet.UsedRange command ('Parent' means Sheet1 in this case) overrides the used range on the sheet.
If we are dealing with large volumes of data, this can reduce the file size (try to save the file and see its size with this line and without one)

hi nilem (or anyone else who can respond)

in looking at this approach the light finally came on and why i understand why the sort first works......my concern is that lets suppose i have a condition where even with the sort i reach the limit of "changes" which i guess "could" happen if the file is large enough.........

so what is a better way to delete rows where the cells in a given column do not equal a specific text string

any help would be appreciated.

thanks.

went through the thread again.......looks like nilem already posted a few examples.......so i will review them now......ment to do that a few weeks ago.........but if someone has a good way to delete rows from a large file that works good let me know.

what exactly is a non contiguous range.

if i have say 100k rows in a column and there are only two values in the column say value1 and value2
but that value1 and value2 are alternating........how many non-contiguous range values does this represent?

i am trying to figure a work around for this filter thing and i was thinking if i created a col of values say "delete" and "not delete" then deleted rows using the filter applied to this added column would i still have the problem

Couple more questions:

So in original problem was due to the "8192 specialcells limit for non-contiguous" ranges. In reading about this it sounds like all you have to do is a sort prior to the autofilter to make the problem go away........which if you recall is exactly what i did to get my code to run. SO here is my question...........my prior sort fixes the problem but the "8192-limit" is still there right? ie.....the sort worked for me in my particular situation but it might not because in my sort column if i had 8192 different values then iwould again reach that limit.............right? just want to make sure i understand this "8192-limit" issue and. See in my mind i really need to find an alternative to what i originally did ................i have been doing some reading and what i have found as a soln is this
1) insert a new column
2) insert a TRUE FALSE formula into the column
3) sort the table based on that column
4) delete cells with either TRUE or FALSE, in one block.





the code samples in posts: #47, #49 and #50

Can these run using 2007 excel and do these also suffer from the "8192-special cells limit" issue?

thanks..........

David,
Please the attached if they are any helpful

http://www.rondebruin.nl/win/s4/win003.htm

As long as you use filtering which also in turns uses specialcells, you still have problems if your excel version is pre 2010.
Please see what Ron's said. This problem is fixed in 2010.
You can use loop to completely avoid the limit of 8192 if you do not mind the speed. The only reason I can think of using a filer is for speeding purpose, but a loop can also do the job.

Jay,
You probably could do better explanation than I did to David on what these terms mean: Specialcells 8192 limit and non contiguous range

thanks for repsonding and thanks for the information.

so anything which has the specialcells or filter will have the 8192 limitation. So the code posted in #47 and #49 should be fine then as they dont have a reference to special cells or filter.

Originally Posted by JosephP

#49 does refer to specialcells

but in that case specialcells consists of one range