VBA to clear duplicate dates in column but keep row

Tocix

New Member
Joined
Apr 17, 2016
Messages
25
Good morning,

Please help me out with macros that would cleared out duplicate dates on column A but keep all rows. For example clear out the one I have it in red.

ABCD
110/15/2019lesson plansTBATBA
210/15/2019
homework
TBATBA
310/15/2019extra credit
TBATBA
4
510/16/2019TBATBATBA
610/16/2019TBATBATBA
10/17/2019TBATBATBA

<tbody>
</tbody>


Thanks
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,579
Office Version
2019
Platform
Windows
Try applying conditional formatting to the used part of column A. Use =A1=A2 as the rule, then set the font colour the same as the fill colour.

This will hide the date instead of removing them, which is more useful if you need to summarise your data by date at a later point.
 

Tocix

New Member
Joined
Apr 17, 2016
Messages
25
When I copied to another sheet the duplicate dates still carried over. I just want to clear it out completely that way my other sheet only have one date shown.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,579
Office Version
2019
Platform
Windows
The conditional formatting will carry over as well so the duplicates will be hidden in the copy.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,684
Office Version
365
Platform
Windows
Try
Rich (BB code):
Sub Remove_Dupes()
  Dim r As Long
  
  For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("A" & r).Value = Range("A" & r - 1).Value Then Range("A" & r).ClearContents
  Next r
End Sub
 

Tocix

New Member
Joined
Apr 17, 2016
Messages
25
Try
Rich (BB code):
Sub Remove_Dupes()
  Dim r As Long
  
  For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("A" & r).Value = Range("A" & r - 1).Value Then Range("A" & r).ClearContents
  Next r
End Sub
Works perfectly! thank you very much for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,333
Messages
5,467,992
Members
406,562
Latest member
Isbenji

This Week's Hot Topics

Top