Delete rows based on duplicate cell, but leaving first and last duplicate.

**LadyNicole** · 05-27-2013, 08:30 AM

Hi guys, I have a problem which might seem simplistic,but its not ( for me at least ). I want to have a working macro that deletes a row based on the column cells 'P' which has only 1's and 2's in them. I want to have the macro keep the first '1' and the last '1' but delete all the '1's' in between, and then do the same for the '2's' and to make it even more complicated, every time the 1's change into a group of 2's the macro should keep the first '1' and last '1' again. This is difficult to explain, sorry.

Cannot attach a sample document,I am new to this site and it seems to throw out a error when I try uploading my excel sheet I'm working on.

Here is an an example of what I'm struggling for the code to-do.

Row---> Column P________________________________________New applied macro on rows
------------1____________________________________________ ------------1
------------1____________________________________________ -Deleted and shifted up row-
------------1____________________________________________ -Deleted and shifted up row-
------------1____________________________________________ ------------1
------------2____________________________________________ ------------2
------------2____________________________________________ -Deleted and shifted up row-
------------2____________________________________________ -Deleted and shifted up row-
------------2____________________________________________ ------------2
------------1____________________________________________ ------------1
------------1____________________________________________ -Deleted and shifted up row-
------------1____________________________________________ -Deleted and shifted up row-
------------1____________________________________________ ------------1

This is the macro code I am currently using that does not do exactly what I'm looking for but its a start.

Please Login or Register  to view this content.

Thanks, I hope there is someone out there willing to help me with this, would truly appreciate it.

**arlu1201** · 05-27-2013, 09:37 AM

Will you always have 4 rows of the same type of data and 2 need to be deleted?

Did you try attaching the excel file this way?

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**LadyNicole** · 05-28-2013, 03:56 AM

Hi Arlu , thanks for shifting the thread for me

I managed to get my excel document loaded.
With this book I will always have the same amount of columns, but the rows can run to quite a large amount.
I'm trying to get the macro to work on the deciding column 'P' with the heading '' Drill position''
Keeping the row with the first '1' in it and deleting the other rows with '1' ,but leaving the last '1' until the next '2'

I am attaching another document where I deleted manually what I want the macro to do for me just as an example.

I am using Excel 2010

Thank you, I hope there is someone out there who could lend a helping hand with this, unfortunately I am on a bit of a tight schedule to produce graphs with this information so really keeping thumbs up in the hope that this thread gets some views. Any help I can get would be greatly appreciated.

**AlphaFrog** · 05-28-2013, 04:34 AM

This uses a formula in column U to determine which rows to delete. Then autofilters on the results of that formula and deletes en masse the filtered rows.

Please Login or Register  to view this content.

**LadyNicole** · 05-28-2013, 05:07 AM

Thank you AlphaFrog !! thank you and that clever mind of yours , this works like a absolute charm. I double checked the rows and there seems to be no problem at all. Oh, you just made my day

. Thanks to you my deadline seems allot more possible to reach. I wish you have a splendid day further ^^ Such talented people out there in the world who shares their talent with those in need out of pure goodwill.

I believe this thread is closed and solved then