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.
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
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.
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 -
Thank You,
Please Login or Register to view this content.
Deep
I would like to delete both, thanks again so much!!
Here is the code then, Just 1 line added -
Thank You,Please Login or Register to view this content.
Deep
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
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).
Ciao,Please Login or Register to view this content.
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
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?
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:
Ciao,Please Login or Register to view this content.
Holger
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
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.
Ciao,Please Login or Register to view this content.
Holger
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..
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
Ciao,Please Login or Register to view this content.
Holger
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks