Remove duplicates across tabs (1 Viewer)

mbhw99

Registered User.
Local time
Today, 07:42
Joined
Apr 3, 2012
Messages
55
Hello.

I am using Excel '07. My spreadsheet has two tabs that get data from access. How do I get values (Employee Name) from the 2nd tab (Roster) removed if they show up in the first tab (NL)?

The NL tab is filtered as well to show names from a previous month. Will the code work with the filter?
 

Trevor G

Registered User.
Local time
Today, 12:42
Joined
Oct 1, 2009
Messages
2,341
Something like this will work, change the sheet names in the code

Sub DeleteDuplicates()
Dim Row As Long
Dim FoundDup As Range

Sheets("New").Select 'Remove duplicates from this sheet

For Row = Range("A65536").End(xlUp).Row To 2 Step -1

Set FoundDup = Sheets("Old").Range("A:A").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole)

If Not FoundDup Is Nothing Then
Cells(Row, 1).EntireRow.Delete
End If

Next Row

End Sub
 

mbhw99

Registered User.
Local time
Today, 07:42
Joined
Apr 3, 2012
Messages
55
Attached the code to a button, but it doesn't remove the duplicates. It just switches to the sheet I want the duplicates removed from.
 

Trevor G

Registered User.
Local time
Today, 12:42
Joined
Oct 1, 2009
Messages
2,341
Can you attach an extract of the spreadsheet I can take a look and post a solution.
 

mbhw99

Registered User.
Local time
Today, 07:42
Joined
Apr 3, 2012
Messages
55
here ya go.

Just a summary of where data is coming from in these sheets:

NL: pulls names from write-ups. Names are filtered from last month.

Roster: pulls from a complete roster minus supervisors.

Thank you for your time.
 

Attachments

  • SMC Roster & Excludes.zip
    18.2 KB · Views: 379

Trevor G

Registered User.
Local time
Today, 12:42
Joined
Oct 1, 2009
Messages
2,341
You need to adjust a few things in the code. Attached is a working copy, I have tested it several times.
 

Attachments

  • Copy of SMC Roster & Excludes 2.zip
    26.1 KB · Views: 303

Users who are viewing this thread

Top Bottom