Finding duplicates using conditional formatting and deleting them in VBA

[taquitoxz]

is there anyway to reference a cell based on conditional formatting ? for example, A1 is John, B1 is Fish, C99 is John. I perform conditional formatting on column A and column C, and once John is found to be a duplicate in A1, I want to delete the adjacent cell, which is Fish in B1. How can this done with VBA coding? THanks.

 

[erik.van.geit]

Hi,

Do you need to do this often or just once?
Please provide a larger example, let's say 3 columns, 10 rows. Copy your excel sheet and paste it within your post.

"conditional formatting" has nothing to do with clearing a cell, or did you really mean "delete"?

kind regards,
Erik

 

[taquitoxz]

col1	col2	col3
b	c	fish
c		cat
d	b	dog

<tbody>
</tbody>

Basically i have col1 and col3, then i am given col2, i want to perform condition format duplicate search on col1 and col2, which will give me "b" and "c" as the answers in col1. based on these 2 answers, I want to delete their corresponding contents in col3, i.e. fish and cat should be removed (meaning those cells to be cleared/be blanks). Now the issue is how can I make all these possible in VBA coding instead?

 

[erik.van.geit]

This works for me:

Option Explicit

Sub ClearItemInColumnCWhenColumnAHasDuplicatesInB()
'140203
'Erik Van Geit

'will clear the contents of column C when column A has duplicates anywhere in column B
'CAVEAT
'If the sheet could already have an autofilter, this code must get some additional lines.

'START WITH
'   A        B        C
 '1 Header A Header B Header C
 '2 G                 item 1
 '3 A                 item 2
 '4 B                 item 3
 '5 F        B        item 4
 '6 F                 item 5
 '7 E        A        item 6
 '8 D                 item 7
 '9 A        D        item 8
'10 G                 item 9
'11 H                 item 10
'12 G                 item 11

'TO GET
'   A        B        C
 '1 Header A Header B Header C
 '2 G                 item 1
 '3 A
 '4 B
 '5 F        B        item 4
 '6 F                 item 5
 '7 E        A        item 6
 '8 D
 '9 A        D
'10 G                 item 9
'11 H                 item 10
'12 G                 item 11

Dim LRColA As Long
Dim LRColB As Long
Dim LC As Long

    With Sheets("Sheet1")
    LRColA = .Cells(Rows.Count, "A").End(xlUp).Row
    LRColB = .Cells(Rows.Count, "B").End(xlUp).Row
    LC = .Cells(1, Columns.Count).End(xlToLeft).Column
    
        With .Range(.Cells(1, LC + 1), .Cells(LRColA, LC + 1))
        'could leave out the IF( ... ,REMOVE KEEP) and filter using TRUE
        .FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC1,R1C2:R" & LRColB & "C2,0)),""REMOVE"",""KEEP"")"
        .AutoFilter Field:=1, Criteria1:="REMOVE"
        End With

    .Range("C2:C" & Rows.Count).ClearContents
    .Columns(LC + 1).Delete
    End With

End Sub

If you want to learn something, step through the code using function key F8

best regards,
Erik