VBA code remove duplicates from a filter

Wafee

Board Regular
Joined
May 27, 2020
Messages
104
Office Version
  1. 2013
Platform
  1. Windows
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 AColumn B
VBAYes
VBANo
VMA
VCANo
VDAYes
VDA

Once conditional formatting is applied.

Column AColumn B
VBAYes
VBANo
VMA
VCANo
VDAYes
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 AColumn B
VBANo
VMA
VCANo
VDA
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Why not just use the recorder to record yourself doing it manually, that way you will have the basis of the code.
 
Upvote 0
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.
 
Upvote 0
If you record a macro & then post the resultant code, we can clean it up & make it dynamic for the number of rows.
 
Upvote 0
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.

VBA Code:
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.
 
Upvote 0
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?
 
Upvote 0
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"
 
Upvote 0
Ok, how about
VBA Code:
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
 
Upvote 0
Ok, how about
VBA Code:
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.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top