Delete duplicate rows based on values of cells

[phixate]

I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process. Any suggestions?

A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

or

A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

[oeldere]

c2=

Please Login or Register  to view this content.

d2=

Please Login or Register  to view this content.

and drag both down.

After that you can filter on column D.

Select the values delete (and after that delete those rows).

[phixate]

Hmmm I don't think that's what I'm looking for. Picture Column B as a SKU number for a product and Column A as a binary flag for available or not. There's actually a third column I didn't put in for Size. I want to remove all rows for products available only in 1 or 2 sizes.

Avail---SKU---Size
0------123----10 <-delete
1------123----11 <-delete
0------123----12 <-delete
0------123----13 <-delete
0------123----14 <-delete
0------123----15 <-delete
1------321----10
1------321----11
0------321----12
1------321----13
0------321----14

[kalak]

Could you be a bit more explicit about your criterion (or criteria if more than one) for what's to be deleted and what isn't?
Your explanation isn't clear to me.

[phixate]

OK I got this working for me. Thanks so much! This has helped us implement a threshold for size availability in our inventory. I never realized the power of a countifs statement.

[oeldere]

Thanks for the reply.

Glad I could help.