VBA code remove duplicates from a filter

[Wafee]

Can someone help me with VBA code that does the following. I will explain what I have been doing manually in the sheet.

will apply conditional formatting to highlight duplicates in column A. Will filter "yes" from column B and then will filter by color of red from A (highlighted ones from conditional formatting) and any resulted rows needs to be deleted. If I found nothing we can leave it as it is.

Sample Data: Blanks in "B" column should be considered as NO.

Column A	Column B
VBA	Yes
VBA	No
VMA
VCA	No
VDA	Yes
VDA

Once conditional formatting is applied.

Column A	Column B
VBA	Yes
VBA	No
VMA
VCA	No
VDA	Yes
VDA

Yes in Column "B" and then respective rows of any highlighted cells in column "A" must be removed from the sheet.

the out put will be as below.

Column A	Column B
VBA	No
VMA
VCA	No
VDA

 

[Fluff]

Why not just use the recorder to record yourself doing it manually, that way you will have the basis of the code.

 

[Wafee]

Why not just use the recorder to record yourself doing it manually, that way you will have the basis of the code.

Hi Fluff,

I want it be a dynamic hence not going for recording. would be greatful if you can help me with a code that does the job. code doestn't need to follow the baove steps.

 

[Fluff]

If you record a macro & then post the resultant code, we can clean it up & make it dynamic for the number of rows.

 

[Wafee]

If you record a macro & then post the resultant code, we can clean it up & make it dynamic for the number of rows.

Hi Mate,

Here it is.

Sub Macro2()

    Columns("B:B").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("B1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AW$64").AutoFilter Field:=2, Criteria1:=RGB(255, _
        199, 206), Operator:=xlFilterCellColor
    ActiveSheet.Range("$A$1:$AW$64").AutoFilter Field:=7, Criteria1:="<>"
    Rows("14:36").Select
    Selection.Delete Shift:=xlUp
    Range("B1").Select
    Selection.AutoFilter
End Sub

thank you in advance.

 

[Fluff]

That is not doing what you said you needed, aside from the fact that the columns are different, you are filtering for non blank cells, rather than for "Yes".
Which should it be?

 

[Wafee]

That is not doing what you said you needed, aside from the fact that the columns are different, you are filtering for non blank cells, rather than for "Yes".
Which should it be?

Sorry, it should only filter for "Yes"

 

[Fluff]

Ok, how about

Sub Wafee()
   With Range("B1", Range("B" & Rows.Count).End(xlUp)).FormatConditions
      .Delete
      .AddUniqueValues
      .Item(.Count).DupeUnique = xlDuplicate
      .Item(.Count).Font.Color = -16383844
      .Item(.Count).Interior.Color = 13551615
   End With
   With ActiveSheet
      .Range("A1:AW1").AutoFilter 2, RGB(255, 199, 206), xlFilterCellColor
      .Range("A1:AW1").AutoFilter 7, "Yes"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[Wafee]

Ok, how about

Sub Wafee()
   With Range("B1", Range("B" & Rows.Count).End(xlUp)).FormatConditions
      .Delete
      .AddUniqueValues
      .Item(.Count).DupeUnique = xlDuplicate
      .Item(.Count).Font.Color = -16383844
      .Item(.Count).Interior.Color = 13551615
   End With
   With ActiveSheet
      .Range("A1:AW1").AutoFilter 2, RGB(255, 199, 206), xlFilterCellColor
      .Range("A1:AW1").AutoFilter 7, "Yes"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

Hi Fluff. That works perfectly. Thank you soo much.
Would be great if you can help me with another removing duplicates scenario titled "Removing Duplicates based on some criteria". Thank you in advance.

 

[Fluff]

You're welcome & thanks for the feedback.