Finding duplicates using conditional formatting and deleting them in VBA

taquitoxz

New Member
Joined
Jan 6, 2014
Messages
9
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
Jan 6, 2014
Messages
9
col1
col2col3
bcfish
ccat
dbdog

<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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
This works for me:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

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
Top