Excel 2007 - SpecialCells error

[Shigueo]

Hello fellows,

I've been going through a harsh time programming some VBAs.
I'm gonna try to be succinct.

I have a database with lots of errors which i want to delete the rows where the errors are found.

The code i'm using is simply:

Columns(1).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

That works FINE when I have a small range of errors (i'd say 65k~)

though when i use it to normalize a set of over 200k~ rows, it simply deletes ALL the rows.

A simple test would be to fill a worksheet with:

#N/A
0
#N/A
0

All the way to row 200k.

You might also see that if you select the entire column, press F5, and select Constans -> Errors Excel says that the selection is too big.

A simpler solution would be to delete row by row as I inspect each one of them, but it is exponentially more time consuming than using specialcells method.

Has anyone come accross this ever? Do you have any suggetions?

 

[Lewiy]

Row by row might take longer, but if you turn off screen updating and set calculation to manual at the start of the code (and reverse that at the end) then it will run much more quickly.

 

[GlennUK]

I've got a feeling that you'll have to process in 65K lumps ( a lot faster than 1 row at a time ). Is that a valid suggestion for you? ( remember to process in lumps from the bottom upwards if you follow this course of action ).

 

[Domenic]

Have a look here for a workaround...

http://www.rondebruin.nl/specialcells.htm

 

[Shigueo]

You guys are so effective

Thanks a lot, I was hoping someone had a magical solution, but i guess I'll have to lump, just as mentioned. I didn't know the limit was 8192 ares, which is pretty helpful already.

I'm gonna try to close the topic.

 

[Shigueo]

Can't close it apparently...

Though I consider it solved.

Regards,

 

[Shigueo]

Here is the code added.

It works just fine.

const_Height = 8000
k = Int(i / const_Height) + 1
a = 0

Do While (a < k)
Range(Cells(1 + (k - a) * const_Height, 1), Cells((1 + (k - a - 1) * const_Height), 1)).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
a = a + 1
Loop

Where
'i' is the last row of the sheet
'a' is an auxiliary variable to loop
'const_Height' the is height of the lump i'm using to analyze
'k' is the number of lumps in the sheet

Regards,