+ Reply to Thread
Results 1 to 15 of 15

Deleting double-consecutively repeated columns and leaving 4-consecutively repeated column

  1. #1
    Registered User
    Join Date
    05-10-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    26

    Deleting double-consecutively repeated columns and leaving 4-consecutively repeated column

    Hi,
    I was wondering if I could figure out a macro to do the following:

    Ex:
    142321
    142321
    999999
    999999
    999999
    999999
    121421
    121421
    241424
    241424
    241424
    241424

    So here, I would like to delete ones that are double-consecutively repeated (i.e. 142321) and leave only the ones that are repeated 4 times (i.e. 9999)

    Thanks in advance for your help.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Does the column need to be sorted before the Macro applies??

    For Example -

    142321
    142321
    999999
    999999
    121421
    121421
    241424
    241424
    241424
    241424
    999999
    999999

    The 4 999999z are not together, would you still want to delete them??

    Thank You,

    Deep
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    05-10-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Thanks for the reply, and its sorted beforehand so I just need to delete the ones that have 2 consecutive repeats and leave 4 consecutive repeats alone.
    Last edited by exlgh91; 05-10-2013 at 10:40 AM.

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    And do you want to delete both the Values or Keep one of the Duplicate?

    If you want to keep 1 of the duplicate, below is the code -

    Please Login or Register  to view this content.
    Thank You,

    Deep

  5. #5
    Registered User
    Join Date
    05-10-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    I would like to delete both, thanks again so much!!

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Here is the code then, Just 1 line added -

    Please Login or Register  to view this content.
    Thank You,

    Deep

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    You can change the range as per your requirement though.

    The code is written only for the A Column.

    I've attached the file too.

    Thank You,

    Deep
    Attached Files Attached Files

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,185

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Hi, Deep,

    avoid problems with the counter when deleting by working from the bottom up (otherwise you may miss a couple of rows in the check).

    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  9. #9
    Registered User
    Join Date
    05-10-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Hello everyone, I've tried the following code,

    Dim longCounter As Long

    With Worksheets("MAY 8")
    For longCounter = Cells(rows.Count, 1).End(xlUp).Row - 1 To 1 Step -1
    If Application.WorksheetFunction.CountIf(Range("Q:Q"), Cells(longCounter, 1).Value) = 2 Then
    Range(Cells(longCounter - 1, 1), Cells(longCounter, 1)).EntireRow.Delete
    End If
    Next longCounter
    End With


    It doesn't seem to delete the 2 consecutive repeated ones, any reasons?

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,185

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Hi, exlgh91,

    because you didnīt use code-tags when posting here? (just kidding)

    The code will be executed on the active sheet although you want it to execute on a non-active sheet:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  11. #11
    Registered User
    Join Date
    05-10-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Hi Holger, sorry to bother you again but still no luck.
    I have attached a sample file, where I want to use column B data for consecutive repetitiveness.
    Much thanks in advance!Test.xls

  12. #12
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,185

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Hi, exlgh91,

    Cells wants the Row first and the Column thereafter either being noted as a string or as the Column number. A is 1 so you were checking the wrong column for the dupes. And I should have noticed that checking Column Q with a value from Column A doesnīt make that much sense.

    This code will check for Column B, if you want it to work on any other column just change the value of the Constant cstrCOL.
    Please Login or Register  to view this content.
    Ciao,
    Holger

  13. #13
    Registered User
    Join Date
    05-10-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    This is great much thanks! Just a quick question though, it works by deleting everything other than 4 repetitives, but still leaves 2 irrelevant ones at the bottom (does this have something to do with -1 TO 1 Step -1? and what does it mean?). Wish I could buy you a pitcher of beer or something..

  14. #14
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,185

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Hi, exlgh91,

    the code was exclusively looking for doubled lines by using WorksheetFunction.CountIf(..) = 2. If you want it changed the code must be altered as the lines to delete could interfere with other existing lines which should not be deleted.

    Maybe you go have a go with
    Please Login or Register  to view this content.
    Ciao,
    Holger

  15. #15
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Deleting double-consecutively repeated columns and leaving 4-consecutively repeated co

    Hey Holger!!

    I forgot the lesson you taught me a few days back regarding going reverse when it comes to deleting rows..

    Ill keep tha in mind..

    Cheers!!

    Deep

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1