Trouble getting syntax for removal of duplicates correct!

[Vonsteiner]

Greetings my fellow Excel users,

I'm having trouble with a macro I'm writing. Here are the workbooks and steps I am writing it for:

• Data File 1 - External data file with current subscribers (DF1)
• Data File 2 - A local workbook used to store past data. Two worksheets are used (DF2Past & DF2Current)
• Data File 3 - External data file used to compare if DF2Past account #s are still current (DF3AcctComp)
• Data File 4 - A local workbook used to clean current data then input in separate workbook (DF4)

These are the steps I need to complete. Some I have already written but a few I need assistance on:

1. Copy all data (including headers) from DF1 and paste into DF2Current - step written and working
2. Delete all rows (except headers) from DF4 - step written and working
3. Copy all data (except headers) from DF2Past to DF4. See Example 1 - step written and working
4. Create DF3AcctComp worksheet with 'Accounts' in "A1" & 'Compare' in "B1". See example 2 - step written and working
5. Copy all account #s with cell color 'blue' from DF4 into DF3AcctComp into "A2" - step written and working
6. Insert VLOOKUP formula into "B2" of DF3AcctComp to see if account is listed and copy formula down - step written and working
7. Check for any return of "#N/A" in column B and list those account #s in column D starting in "D2" (if none are "#N/A" then I can skip step 8) - Need help with this step
8. Each account # resulting in "#N/A" needs to be deleted from the account #s (cell color 'blue') from DF4 (the whole row needs to be deleted) - Need help with this step
9. Copy all data (except headers) from DF2Current to DF4 cell "A2" as values - step written and working
10. Remove any duplicate account #s from DF4 (delete the whole row). All columns must be duplicate. See example 3 - Need help with this step

Example 1

DF2Past Data
Account #	Address	City	State
5001	123 Some St	Somewhere	US
5002	456 No St	Nowhere	US
5003	789 Here St	Herethere	US
5004	123 Out St	Outthere	US
5005	456 Some St	Somewhere	US
5006	789 No St	Nowhere	US
5007	123 Here St	Herethere	US
5008	456 Out St	Outthere	US

Example 2

Look up data		DF3AcctComp Data
Account #	Data	Data	Account	Compare
5005	X	Y	5005	5005
5006	XX	YY	5006	5006
5007	XXX	YYY	5007	5007
			5008	#N/A
Acct 5008 would be	removed	from acct# list in	DF4	'blue' cell color

Example 3

DF4 Data
Account #	Address	City	State
5009	789 Some St	Somewhere	US
5010	123 No St	Nowhere	US
5006	789 No St	Herethere	US
5005	456 Some St	Somewhere	US
5005	456 Some St	Somewhere	US
5006	789 No St	Nowhere	US
5007	123 Here St	Herethere	US

As you can see acct# 5008 has already been removed. Now acct#5005 needs to be deleted as it is duplicated across all columns. Acct# 5006 would not be deleted as it has a different city. Here is the code I have tried to remove duplicates in step 10.

Sub duperemove()

lastrow4 = ActiveSheet.Range("A2").CurrentRegion.Rows.Count
Set UsedRange = Range("A2:X" & lastrow4)

ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24), Header:=xlYes

End Sub

Any help would be greatly appreciated. Thank you.