Delete Row in VBA

[Ignition1]

What is the code I need to delete a row if that row contains a value in Column W which is other than "Live"?

So I tried -

If Columns("W").Value = "Dormant" Then EntireRow.Delete

Which is clearly wrong. I had a look on the net but it didn't help. Bit stumped on this one.

Edit - I should point out that "Dormant" is one of about 5 values which isn't "Live"

 

[RSXchin]

if cells(Put_the_row_here,"W").Value <> "Live" Then EntireRow.Delete

 

[VoG]

Try

Sub ColW()
Dim LR As Long, i As Long
LR = Range("W" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("W" & i).Value <> "Live" Then Rows(i).Delete
Next i
End Sub

 

[wigi]

In other topics, you will find lots of code to do these kind of operations. Only 2 minutes I ago: http://www.mrexcel.com/forum/showthread.php?p=2697868#post2697868

 

[GTO]

...

 

[Ignition1]

Thanks VoG - that code worked perfectly, though I don't understand it entirely.

rsxchin's looks quite straight forward but not sure how I would go about referring to a range of rows.

I'm assuming it would go -

if cells(2:2000,"W").Value <> "Live" Then EntireRow.Delete

Though it returned an error.

 

[VoG]

Mine just loops backwards through the range. This should be faster

Sub Del_W()
With Columns("W")
    .AutoFilter field:=1, Criteria1:="<>Live"
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[Ignition1]

That one I understand (sort of). Thanks again.

I'm about half way through my VBA for Dummies book...Offset, With...that should all be clear to me by the end!

 

[Ignition1]

I've been modifying that code to work at filtering out other rows but I've come across an issue with deleting any rows above a certain date.



Sub deletedate()
Dim LR As Long, i As Long
Range("Z2:Z3000").Select
Selection.NumberFormat = "dd/mm/yyy;@"
LR = Range("Z" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Range("Z" & i).Value > 1 / 12 / 2012 Then Rows(i).delete
Next i
End Sub

It deletes all the rows!

 

[wigi]

It deletes all the rows!

Obviously, since 1 divided by 12 divided by 2012 is a very small number.

Use for instance:

DateSerial(2012, 12, 1)

to compare to. There are other possibilities too, like having the date as a string.

Wigi