[Poonam]

We maintain MIS in excel for sending out letters on behalf of Bank for people who have taken loan.

Therefore there is one Borrower and 2 to 15 Co-Borrowers, who have same account number and same outstanding amount and the excel that we receive from Bank have following fields:
A B C D E F G H I
1 Account No. Outstanding Amt Borrower Name Bo -1st Address Bo - 2nd Address Co-Bo Name Co-Bo -1st Address Co-Bo - 2nd Address Co-Bo -3rd Address and so on....
2 3456 90,000 James ABCD St. XYZ St. Kofi 123 St. 1256 St. 675 St.
3 8970 100,000 John Big St. Lucy qty St. GTY St.

Now we need address same letter to all the Borrowers and Co-Borrowers, so we need to print envelopes for these letters with the account no. mentioned on all envelopes.

We have made one Word Document in the name of Bank Envelope where we have 4 fields on each page of one document only:

1 page have: 1) {MERGEFIELD Account_No.} 2) {MERGEFIELD Borrower_Name} 3) {MERGEFIELD Bo_1st Address} 4) {MERGEFIELD Outstanding_AMT}

2 page have: 1) {MERGEFIELD Account_No.} 2) {MERGEFIELD Borrower_Name}3) {MERGEFIELD Bo_2nd Address} 4) {MERGEFIELD Outstanding_AMT}

3 page have: 1) {MERGEFIELD Account_No.} 2) {MERGEFIELD Co_Borrower_Name} 3) {MERGEFIELD Co_Bo_1st 4) {MERGEFIELD Outstanding_AMT}Address}

4 page have: 1) {MERGEFIELD Account_No.} 2) {MERGEFIELD Co_Borrower_Name} 3) {MERGEFIELD Co_Bo_2nd Address} 4) {MERGEFIELD Outstanding_AMT}

5 page have: 1) {MERGEFIELD Account_No.} 2) {MERGEFIELD Co_Borrower_Name} 3) {MERGEFIELD Co_Bo_3rd Address} 4) {MERGEFIELD Outstanding_AMT}

So when we mail merge entire information related to one row is merged together i.e. means if i have 2 accounts then A1 to A2 and each A1 will have 5 pages in word A2 will have 5 pages and so on...

In all the Rows - Account No., Outstanding Amt, Borrower Name and Bo-1st Address is always there but Co-Bo names and addresses varies.

So now when we mail merge the word document for printing looks like below:

Schedule A

Page 1: 3456 - James - ABCD St. - 90,000

Page 2: 3456 - James - XYZ St. - 90,000

Page 3: 3456 - Kofi - 123 St. - 90,000

Page 4: 3456 - Kofi - 1256 St. - 90,000

Page 5: 3456 - Kofi - 675 St. - 90,000

Page 6: 8970 - John - Big St. - 100,000

Page 7: 8970 - Lucy - 100,000

Page 8: 8970 - Lucy - qty St. - 100,000

Page 9: 8970 - Lucy - 100,000

Page 10: 8970 - Lucy - GTY St. - 100,000

So when we print Page 7 and Page 9 are printed n wasted as we don't need them.

Now our requirement is: if in Row A1 - all D1, E1, F1, G1, H1 & I1 fields are filled 5 pages should get mail merged but if in Row A2 - D2, F2, G2 & I2 are filled and E2 & H2 are empty then only 3 pages should get mail merged.

So final document for printing will be 8 pages instead of 10 pages i.e.

Schedule B

Page 1: 3456 - James - ABCD St. - 90,000

Page 2: 3456 - James - XYZ St. - 90,000

Page 3: 3456 - Kofi - 123 St. - 90,000

Page 4: 3456 - Kofi - 1256 St. - 90,000

Page 5: 3456 - Kofi - 675 St. - 90,000

Page 6: 8970 - John - Big St. - 100,000

Page 7: 8970 - Lucy - qty St. - 100,000

Page 8: 8970 - Lucy - GTY St. - 100,000

So coding need to automatically delete Account No. Name & outstanding Amt on pages where address is empty as seen above in Schedule A and shall delete the empty pages in between as well.

Can you please help with coding.