Results 1 to 7 of 7

Thread: VBA to clear duplicate dates in column but keep row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to clear duplicate dates in column but keep row

    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.

    A B C D
    1 10/15/2019 lesson plans TBA TBA
    2 10/15/2019 homework TBA TBA
    3 10/15/2019 extra credit TBA TBA
    4
    5 10/16/2019 TBA TBA TBA
    6 10/16/2019 TBA TBA TBA
    10/17/2019 TBA TBA TBA


    Thanks

  2. #2
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,740
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to clear duplicate dates in column but keep row

    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.

  3. #3
    New Member
    Join Date
    Apr 2016
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to clear duplicate dates in column but keep row

    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.

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,740
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to clear duplicate dates in column but keep row

    The conditional formatting will carry over as well so the duplicates will be hidden in the copy.

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA to clear duplicate dates in column but keep row

    Try
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Apr 2016
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to clear duplicate dates in column but keep row

    Quote Originally Posted by Peter_SSs View Post
    Try
    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.

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA to clear duplicate dates in column but keep row

    Quote Originally Posted by Tocix View Post
    Works perfectly! thank you very much for your help.
    You're very welcome. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •