Do Loop with variable range

The macro below works between 2 different worksheets. This is adding alternate parts to the first spread sheet. On the first spreadsheet, it adds 13 blank rows, then takes the PN from column B above those added rows, copies it into a different spreadsheet that spits out a set of alternate PNs and some associated data. It then first copy,past values of the alternate PNs back into the original spreadsheet in column B below the original PN, then goes back over and copies the associated data of those alternates (columns D to AC) back into the first spreadsheet. Currently it works, but runs for a long time. The way I read this is that it basically will go through 3,150 Lines (endLocation = 225 * 13 +225. I want it to stop after it reaches a blank cell in Column B. I inherited this macro and have tried a few things that don't work. I think the gotcha is the fact that it adds lines each time, so telling it when it reaches the end is tricky. Any help or ideas is appreciated,

Please Login or Register  to view this content.

Column B is contiguous data before the macro runs. The macro adds 13 blank rows, then goes to the other spreadsheet copies over some alternate PNs, then goes back over and copies over related data. After this macro processes another macro goes through and deletes blank rows.

I am adding a couple of attachments here. I'm hoping this will clarify things Workbook_A contains the macros. The 13 lines are added here, the data copied over, then it drops down, adds 13 lines, copies the data etc. Currently it is repeating the process 225 times I believe. In this particular data set I'd want it to end after 94 times, but that is the variable.

"So you copy the Prime DPN from column B of worksheet 'Sheet2' to B2 of worksheet 'Family Tool'." - Correct Cell B2 to Cell B2

"You then copy the data produced in columns D:AF family tool to column C of worksheet 'Sheet2' starting in the same row as the Prime DPN that had been copied." Almost, Data produced is copied in 2 sets. First the data is copied from C2:C14 to column B of worksheet 'Sheet2' below the Prime DPN (in this case starting in B3. Then it goes back and copies Range D2:AF14 to starting Cell D3.

Yes, 13 rows are always inserted. Prime DPNs have differing numbers of alternates. Some have none and some have up to 13. In this case only 4 rows of data would actually be copied (yes it is copy values only). After this macro completed another one runs and deletes all the blank rows.

Hotlist Template V3_0_SEND.xlsm

Here is a final version. I deleted a bunch of the rows. The yellow highlighted rows are the Prime PNs and the non-highlighted rows are the alternates that were copied in. All of the blank rows have been deleted.

I inherited this macro. For me, I don't think it would be easier to just add the qty of rows needed. That would require some coding that is beyond my abilities. 13 Rows may be too many to add, but I have not evaluated the # of alternates per prime to see if it is way to many. Besides, anything "extra" is deleted.

Thank you for your interest in helping on this!!!

Thanks, hope it does! I know I've done a lousy job of describing it. I'm new to macros, only been playing with them for about a month after inheriting a few. I've managed to solve a lot of the variable range type things, but this one has me stumped.