How to Delete rows in a column

[Guest]

I have the following data in Columns A and B. I would like to write code for
all rows in columns A and B that:

delete the rows for Example1 since the values in Column B are the same
delete the row for Example 2 since it only has one value
keep the rows for Example 3 since the values in Column B are NOT unique
delete the rows for Example 4 since the value in Column B are the same

Column A ColumnB
Example1 Create PO
Example1 Create PO
Example1 Create PO
Example2 Create PO
Example3 Approve Invoices
Example3 Approve Invoices
Example3 Create PO
Example4 Approve Invoices
Example4 Approve Invoices


Thanks!

 

[Scott]

One possible implementation would be to do as follows:

(It assumes that you've sorted by Column A)

1) Start at the last row, and find all the rows that have the same
value in Col A.

2) Iterate through each of those rows in Col B to determine if they
have only one type of value, or multiple types. (Do this by comparing
all values against the the value in Col B in the last row... if any are
different, you keep)

3) If any were different from step 2, delete all the rows that
correspond to that section.

4) Repeat 1-3, starting one row before the first row from the previous
section.

ie. For your example, last row is row 10. Rows 9-10 have the same
value in Col A. Go through values in Col B, find they are the same.
Delete rows 9-10. Repeat starting in row 8 (9 - 1).

Scott

 

[Scott]

Something like this:

Public Sub removal()
Dim z As Long
Dim y As Long
Dim i As Long
Dim t As String
Dim Same As Boolean
Dim WS As Worksheet

Set WS = Worksheets("Sheet1")
z = WS.Range("A65536").End(xlUp).Row

While z > 1
y = z
While WS.Cells(y - 1, 1) = WS.Cells(z, 1)
y = y - 1
Wend

Same = True
t = WS.Cells(z, 2)
For i = y To z
If WS.Cells(i, 2) <> t Then Same = False
Next i

If Same Then WS.Range(y & ":" & z).Delete

z = y - 1
Wend
End Sub

Scott