How Can I Delete Row Based on Criteria of Cell - VBA?

[coltonspe]

Does anyone know the VBA to delete a row based on the contents of a particular cell?

I have the following formula comparing to another sheet to see if there is a duplicate:

=IF(ISERROR(MATCH(B7,Data!A:A,0))=FALSE, "Y","N")

How can I write the VBA to then take the cells that have a "Y" and delete those rows, leaving only the rows with a "N" in them.

Thanks in advance

(Excel 2010)

 

[JoeMo]

what column are your formulas in and what is the starting cell?

 

[HalfAce]

Hi,
Assuming:
(1) The rows you want to delete are on the active sheet and (2) The "Y" and "N" values are in (say) column D, then you can try something like this.

Sub DeleteTheYs()
Dim LstRw As Long, Rw As Long
LstRw = Cells(Rows.Count, "D").End(xlUp).Row
For Rw = LstRw To 2 Step -1
  If Cells(Rw, "D").Value = "Y" Then Rows(Rw).Delete
Next Rw
End Sub

Hope it helps.

 

[coltonspe]

Formulas are in column A which I will eventually hide starting on row 5