how can I delete rows based on column value

[mike]

How can I delete certain rows of my worksheet that has a specific value in a
specific column. For example, delete all rows that have a value of '0' in
column 'C'??

Mike

 

[Bob Phillips]

apply an autofilter to column C, filter on 0, and then just delete the
visible rows.

 

[StumpedAgain]

The following works for one "0". You can build a loop around this and use
FindNext() to locate others.

Option Explicit
Sub find()

Dim c As Range
Set c = Range("C:C").find(What:="0")
Rows(c.Row).Delete

End Sub

 

[StumpedAgain]

PS. I like bob's idea better (just refreshed the page)

 

[mike]

That still deleted everything.

PS. I like bob's idea better (just refreshed the page)

 

[mike]

I gues I could just sort and delete, that's even simpler. I don't really need
to preserve thorder.

 

[RyanH]

This shoudl do the trick.

Sub DeleteRows()

Dim i As Long
Dim LastRow As Long

LastRow = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row

For i = LastRow To 1 Step -1
If Range("C" & i) = 0 Then
Range("C" & i).EntireRow.Delete
End If
Next i

End Sub

 

[Gord Dibben]

Sub DeleteRows_With_Zero()
FindString = "0"
Set b = Range("C:C").Find(what:=FindString, lookat:=xlWhole)
While Not (b Is Nothing)
b.entirerow.Delete
Set b = Range("C:C").Find(what:=FindString, lookat:=xlWhole)
Wend
End Sub


Gord Dibben MS Excel MVP