Delete Rows with Criteria

[JEHalm]

Hello friends, I am working a spreadsheet and need assistance to delete rows. My spreadsheet contains approximately 750 rows and 5 columns of data. Column A contains accounting lines that has this format (PJE012525, or PNE035757,etc.). The majority of numbers are unique. However, there are about 50 instances where the cells are the same (mostly one copy but a few have three). The list has been sorted so the cells that have the same accounting line are the same in tow or three contiguous sales. I would like to delete the rows where the accounting lines that do not have multiple instances.


Examle


A1 PJE010000 delete row $10,000

A2 PJE011000 delete row $11,000

A3 PJE011100 $12000

A4 PJE011100 $13000

A5 PJE011111 delete row $14000

A6 PJE011112 delete row $14500

A7 PJE011113 $12500

A8 PJE011113 $9750

A9 PJE011113 $12666

A10 PJE011117 delete row $5023


Please advise and as always thanks

 

[Luke M]

You could use this macro:

[pre]

Sub KillRows()
Dim xRow As Integer
'What is the last row?
xRow = Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False

For i = xRow To 2 Step -1
With Cells(i, "A")
If .Value <> .Offset(-1, 0).Value And _
.Value <> .Offset(1, 0).Value Then
.EntireRow.Delete
End If
End With
Next i

Application.ScreenUpdating = True
End Sub

[/pre]
Or, if you want non-macro, use this helper formula:

=AND(A2<>A1,A2<>A3)

copy down, then filter helper column on anything that says TRUE and delete entire row.

 

[JEHalm]

Thanks Luke, that's perfect