Delete rows in separate tabs if string in column A matches

**bonsaitrees** · 11-11-2012, 12:06 PM

Hi,

I have 2 tabs with data in it called hmwk1 and hmwk2. In column A of each tab there is an identifier string, eg 101. I need some code that will look for duplicate strings in column A in both of these tabs. If there are duplicate strings in hmwk1 and hmwk2 delete the corresponding rows in hmwk1 and retain only the first entry in hmwk2. The aim is to have only ONE row with the identifier string in these tabs at the end of the macro. Below is an example using 101 as the identifier string in column A.

Hmwk1 Tab

Column A
101
101

Hmwk2 Tab

101
101

Should result in both 101 rows in hmwk1 and the second row in hmwk2 being deleted.

Hope this is clear.

I'd like to be able to do the same process across three other tabs too called 1's, 2's and 3's. If there are multiple instances of the same identifier keep only 1 row in the highest tab, eg if 101 in column A appears twice in 1's, once in 2's and once in 3's delete all but the one in 3's.

**simeon.hruby** · 11-12-2012, 05:16 AM

bonsaitrees,

I think I might have a macro that will work for you. If you could post the workbook so that I could run it against your actual data and find any errors that would be great!

Simeon

**bonsaitrees** · 11-12-2012, 01:49 PM

Workbook attached. Deleting duplicates in hmwk1 & hmwk2 tabs should be done separately to finding them in 1's, 2's 3's so there could be a duplicate entry in hmwk1 or hmwk2 if it also appears in 1's, 2's or 3's

**bonsaitrees** · 11-13-2012, 02:15 PM

Bump for Simeon to see

**simeon.hruby** · 11-13-2012, 02:59 PM

bonsaitrees,

I am having a difficult time trying to make the macro work as well as I want it to at the moment. But I am still working on it!

Simeon

**bonsaitrees** · 11-13-2012, 03:04 PM

Thank you for replying Simeon. I'll keep popping in

**bonsaitrees** · 11-14-2012, 02:50 PM

**Takes another peek**

**AB33** · 11-14-2012, 03:13 PM

I think this do your first requirement

Sub find_copy()
Dim c2 As Range, c As Range
With Sheets("hmwk1")
LR = .UsedRange.Rows.Count
On Error Resume Next
 For Each c2 In .Range("A1:K" & LR)
   Set c = Sheets("hmwk2").Columns(1).Find(c2, , xlValues, xlWhole)
    If Not c Is Nothing Then
    c2.EntireRow.Delete
    End If
   Next c2
 End With
End Sub

**bonsaitrees** · 11-14-2012, 03:40 PM

Sorry AB32 but it doesn't solve the problem. I inserted 4 duplicates and placed 2 duplicates in hmwk1 and 2 duplicates in hmwk2. It should have deleted both duplicates in hmwk1 and deleted another duplicate in hmwk2, leaving just one behind in hmwk2. At the end the result should be only unique entries when you compare both tabs, with the duplicate in the higher hmwk tab taking priority (hmwk2 over hmwk1). HTH.

**bonsaitrees** · 11-14-2012, 03:43 PM

Ok thank you anyway Simeon for your time and efforts.

it is quite a complex thing to do I think

**simeon.hruby** · 11-14-2012, 03:32 PM

bonsaitrees,

I have messed with it for a while now and I am afraid it is beyond my current skill level. Sorry.

Simeon

Delete rows in separate tabs if string in column A matches

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Re: Delete rows in separate tabs if string in column A matches

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions