Excel 2010: Delete rows based on cell values

[Dafuq]

Should be simple but I'm stuck on this:


I want to clean up large files by deleting all rows where certain cells do (or dont) contain certain values.


I'm fairly new to vba, so I did what I usually do and cobbled up my script from posts here and on other forums, and then resolved the runtime errors that inevitably pop up, but after that I got stuck...


Here's my code so far:

Sub DN_test()


Dim myRange, mySel As Range
Dim delVal As String


delVal = "A"


Set myRange = ActiveSheet.UsedRange.Columns(1).Cells


For Each cell In myRange


If cell.Value <> delVal Then


    If mySel Is Nothing Then
        Set mySel = ActiveCell.EntireRow
    Else
        Set mySel = Union(mySel, ActiveCell.EntireRow)
    End If


End If


Next cell


mySel.Delete


End Sub

If I run it, it deletes the first row (which it should, the cell in the first column doesn't contain an "A"), but that's it. In the following rows, the value of the first cell alternates between "A" and "B", so it should delete half of these as well, but it simply doesn't, it just stops there, no errors.

I suppose the problem has something to do with the union part, but I can't find a solution, as I don't even really know what to google for. I found some posts where people used "Range()" around "ActiveCell.EntireRow" but that just throws another runtime error for me.

Now here's an detail that may be interesting: If I comment out the "mySel.Delete" and then run the code, the first line (which got deleted before I commented it out) is not even selected.

Any ideas?

 

[AlphaFrog]

Change the two...
ActiveCell.EntireRow
to just...
Cell.EntireRow

 

[Dafuq]

Thanks, that was it, I guess that's what you get for "frankensteining" your code together from various sources, I think the script I originally got this from had it in a "do while" loop...

Now while this technically works, it seems to get exponentially more sluggish the more rows it has to chew through. With 5000 rows it finished instantly, with 10000 it took about 15 seconds but when I tried it with more than 20000 it ran for half an hour, I couldn't even CTRL-Break my way out of it, I had to actually kill Excel with the task manager.

The problem is, I will later need to use this with several columns and for files with up to 500000 rows, so if anybody has an idea how to do this more efficiently, that would be greatly appreciated!

 

[Dafuq]

Just in case anyone with the same problem stumbles upon this thread in the future:

I found a solution using AutoFilter, and it is MUCH quicker, it's the second sub on this site:

http://www.rondebruin.nl/win/winfiles/MoreDeleteCode.txt

 

[AlphaFrog]

Just in case anyone with the same problem stumbles upon this thread in the future:

I found a solution using AutoFilter, and it is MUCH quicker, it's the second sub on this site:

http://www.rondebruin.nl/win/winfiles/MoreDeleteCode.txt

Tip:
If you're dealing with a very large data set, a speed improvement to the Autofilter method is to first Sort the data on the criteria column before doing the autofilter. That will consolidate the alike criteria into a contiguous block of rows. Then autofilter-deleting the one contiguous block of rows is much faster than deleting many noncontiguous rows.

 

[Dafuq]

Thanks for the tip, I'm going to do a testrun on a file with 450k+ rows tomorrow, so I'll make sure to implement this first!