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!

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

ALATL wrote:
> 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!

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

ALATL wrote:
> Hmmm..... Yes, I do see that this would be a good method for achieving this.
> Unfortunately... I am new to VBA and don't understand the syntax to achieve
> what you are proposing. :-(
>
> Any more help would surely be appreciated.
>
> Thx.
>
> "Scott" wrote:
>
> > 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
> >
> > ALATL wrote:
> > > 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!
> >
> >