|I guess I should have asked, but when you said you wanted to delete the Address2 line, I had assumed that once you brought the data in, you were done that spreadsheet and wouldn't be loading another address into it. Sort of like opening a invoice template that had all of the formulae, loading in the address and then saving it as a new file.|
The obvious problem is that the code deletes the VLOOKUP function in G6 so it can't pull that data next time.
Of course, you can't have it both ways. You can't have VLOOKUP pull data from a lookup_array, delete the row with the formula and then have it available again next time.
However, I've come up with a formula based workaround that gets the job done. If you don't like this solution, the only other option I can suggest is to do it all with VBA, but then you get into code maintenance issues and all that.
Here's what worked for me:
Take the VLOOKUP formulae from G4:G9 and put them someplace else. You can put them anywhere you want, even on another sheet, in hidden columns, wherever.
I'll use A4:A9 just as an example.
Now, using the example data from your OP, A4:A9 will display:
4 XYZ Company
5 453 Smith Street
7 New York, NY 11220
8 Attn: John
In G4:G9, put these formulae:
G4 and G5 will always display what is in A4 and A5. G6:G9 will display what is in A6:A9 unless there is a 0 in A6. In that case the data from A7:A9 will be shifted up 1 row and G9 will be empty.