Hi Out There,

It's been a very long time since I've had to do any programming and I
never had lot of experience doing anything in Excel as you can see I'm
sure. I need the help of some gurus.

OK I have two workbooks of exported data that I have to clean up. The
first is accounts and the second is contacts. Each account has multiple
rows of contacts in the contact workbook and the Contacts WB contains
"foreign key" column account ID. Many of the accounts in the account
workbook are marked for deletion by a Y/N column and any account that
has to be deleted is assigned an "N". The idea is that before we delete
the accounts I want to search for contacts associated with the
accountID in the Contacts workbook and delete them first. We have an
awful lot of data so automating this process would be of great help.

I created a range in the Accounts WB called "RangeDelete" which selects
the Y/N column. I want to loop through each of these values and for
every "N" grab the Account ID and use it to search the Contacts WB and
then delete that contact row. I also created a range in the Contacts WB
called "AccountIDRange" which selects the account ID column in the
contacts WB.

The code below works fine if there is only one contact row to delete,
but if there are two contacts the For Each loop count gets screwed up.
When the row is deleted, the rows all move up one but the counter is on
the next row. For example I have Contacts on row 3 and row 4 with an
account ID to delete. I delete contact 3 but now row 4 becomes row 3 so
loop counter has passed it by.

Help!! And thanks muchly :)

Here is the code I have:

'Account workbook
ThisWorkbook.Activate

'loop through all the account ID's to be deleted ie value = "N"
For Each myCell In Range("RangeDelete")

If myCell.Value = "N" Then

AccountID = Trim$(Cells(myCell.Row, 1).Value)
ContactWB.Activate

'loop through all the contacts with corresponding account
IDs and delete
For Each myCell2 In Range("AccountIDRange")

If StrComp(Trim$(myCell2.Value), AccountID,
vbBinaryCompare) = 0 Then

ContactWB.Sheets("contacts").Rows(myCell2.Row).Del ete
End If
Next 'next Contact Row

'have to reactivate account workbook
ThisWorkbook.Activate

End If

Next 'next Account ID