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)

 

[mirabeau]

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

Several ways to do that.

With the code given in Post#9 maybe the easiest way is to modify it slightly like

Sub deleterows2()
t = Timer
Dim lr&, lc&, colm, u(), k&, x&, dd
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
    dd = colm(k, 1)
    If (dd = "S") + (dd = "T") + (dd = "U") + (dd = "V") 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
If x > 0 Then [a2].Resize(x, lc + 1).Delete xlUp
MsgBox "Code took " & Format(Timer - t, "0.000 secs")
End Sub

 

[jlkirk]

Thanks

 

[Marcelo Branco]

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

Or
Follow the link i suggested in post #2 and look for Use AutoFilter to delete rows, More than two Criteria

M.

 

[shadow12345]

Stand alone results are about 30% faster .... its amazing the difference in can make. I had something close to the autofilter link already but I was specific on the critia (instead of using a dimmed variable)

 

[Marcelo Branco]

Shadow,

Tks again for the feedback.

All the best

M.