Fastest way to delete rows based on cell

[shadow12345]

Hi all,

I have a sheet with over 50,000 rows. I am looking for the fastest way to check if column M on that row value has a value of "S". If it does then delete the entire row it.

This is the first stage of a macro, i have tried looping though it but it takes for ever! Any other ideas?

Its a header row then just row after row of data (if it helps M with always be either B -- S -- or blank)

 

[Marcelo Branco]

Hi,

Take a look at
http://www.rondebruin.nl/delete.htm

specially in "Use AutoFilter to delete rows" that, i think, is the fastest way.

HTH

M.

 

[shadow12345]

Well thats shaved a good 6 or 7 minutes out of the macro... Cool! Thanks.

 

[Marcelo Branco]

You are welcome!

Just for curiosity, how much time to do the job in 50000 rows?

M.

 

[shadow12345]

was taking about 15 mins but that running it over a terminal server.... on a stand alone machine it was only 3 or 4 minutes.

 

[Marcelo Branco]

3 or 4 minutes seems ok.

Tks for the feedback

M

 

[shadow12345]

yeah it was the terminal server that was killing me... every time i ran it to test i had to wait ages!

Now its only maybe 5 mins on the t server (i haven't test the stand alone yet)

 

[Marcelo Branco]

Please, when you test it on the stand-alone computer tell me something about.

M.

 

[mirabeau]

Hi all,

I have a sheet with over 50,000 rows. I am looking for the fastest way to check if column M on that row value has a value of "S". If it does then delete the entire row it.

You might also like to try this. Maybe fast enough for your purpose.

Sub deleterows()
t = Timer
Dim lr&, lc&, colm, u(), k&, x&
lr = Cells.Find("*", after:=[a1], searchorder:=xlByRows, _
        searchdirection:=xlPrevious).Row
lc = Cells.Find("*", after:=[a1], searchorder:=xlByColumns, _
        searchdirection:=xlPrevious).Column
colm = [m1].Resize(lr)
ReDim u(1 To lr, 1 To 1)
For k = 1 To lr
    If colm(k, 1) = "S" Then x = x + 1: u(k, 1) = 1
Next k
Cells(1, lc + 1).Resize(lr) = u
[a1].Resize(lr, lc + 1).Sort Cells(1, lc + 1), 1, Header:=xlYes
[a2].Resize(x, lc + 1).Delete xlUp
MsgBox "Code took " & Format(Timer - t, "0.000 secs")
End Sub

 

[jlkirk]

What if in addition to "S", you want to delete a row if that cell contains "T", "U", or "V" as well?