Delete row that contains text VBA

[e_sauven]

Hi

I hope someone can help me out I'm very new to VBA!
I have a data set (set of results) that contains text and numbers, occasionally there is a row that just has one cell of text in (a three letter abbreviation that could be ONE OF 17 different abbreviations - OCS, DSQ, BFD, ARB, DGM, DNC, DNE, PTS, RAF, RDG, RET, DNF, DNS, DPI, SCP, UFD, ZFP

If there is a row with a cell containing one of these abbreviations I would like to delete the entire row.

Is this possible?

Another que is if a number appears to be a negative can I delete this cell?

many thanks in advance

Emilie

 

[farmerscott]

Emilie,

1. are your 17 abbreviations all in the same column or are they scattered across your data?
2. when you want to delete a cell (because it has a negative number), do you just want it cleared or do you want the cells moved up or to the right?

FarmerScott

 

[farmerscott]

Emilie,
try this for the first part of your request-

Sub delete_with_double_loop()
Dim x As Long
Dim y As Long
Dim lr As Long
Dim lc As Long

' assumes ColA is the longest column
lr = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' assumes row1 is the longest row
lc = Worksheets("Sheet1").Cells("1", Columns.Count).End(xlToLeft).Column
For y = lc To 1 Step -1
For x = lr To 1 Step -1

If Cells(x, y) = "OCS" Or Cells(x, y) = "DSQ" Or Cells(x, y) = "BFD" Or Cells(x, y) = "ARB" Or Cells(x, y) = "DGM" Or Cells(x, y) = "DNC" Or Cells(x, y) = "DNE" Or Cells(x, y) = "PTS" Or Cells(x, y) = "RAF" Or Cells(x, y) = "RDG" Or Cells(x, y) = "RET" Or Cells(x, y) = "DNF" Or Cells(x, y) = "DNS" Or Cells(x, y) = "DPI" Or Cells(x, y) = "SCP" Or Cells(x, y) = "UFD" Or Cells(x, y) = "ZFP" Then
Rows(x).EntireRow.Delete
End If

Next x
Next y
End Sub

 

[Peter_SSs]

A small set of sample data with an explanation of which rows should be deleted and why might help clarify your request.