Vonsteiner
New Member
- Joined
- Apr 14, 2014
- Messages
- 45
- Office Version
- 365
- Platform
- Windows
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:
Example 2
Example 3
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.
Any help would be greatly appreciated. Thank you.
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)
- Copy all data (including headers) from DF1 and paste into DF2Current - step written and working
- Delete all rows (except headers) from DF4 - step written and working
- Copy all data (except headers) from DF2Past to DF4. See Example 1 - step written and working
- Create DF3AcctComp worksheet with 'Accounts' in "A1" & 'Compare' in "B1". See example 2 - step written and working
- Copy all account #s with cell color 'blue' from DF4 into DF3AcctComp into "A2" - step written and working
- Insert VLOOKUP formula into "B2" of DF3AcctComp to see if account is listed and copy formula down - step written and working
- 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
- 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
- Copy all data (except headers) from DF2Current to DF4 cell "A2" as values - step written and working
- Remove any duplicate account #s from DF4 (delete the whole row). All columns must be duplicate. See example 3 - Need help with this step
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.
VBA Code:
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.