VBA - Repeat Text until reach New Text

Hi,

I'd like some help with the following problem.

I've got some data that sits under heading rows, but I would like to duplicate the heading against the data set that falls under that heading (creating a bit of a database).

In the attached Example workbook, I've got a preview of what I would like in Columns I-L, and the fields I would like to populate with this data in A-D.

The data set is often thousands of rows, so i'd like the macro to repeat until it reaches the end of my data set. The check for the end of the data set would need to check if the next 30 or so rows are blank, terminate (unless their is a function to find where the last cell with data in it sits.

Example Workbook 130628.xlsx

Thanks in advance.

Declan.

Dave,

Thanks for that.

I do however encounter a problem: where the Line number is more than one digit the above code can not handle that.
Also, the code you have written(excuse my ignorance as I am new to VBA) would you classify it as efficient? and how can I repeat it for my entire data set?

Regards,

Declan.

Dave,

Excellent, that handles larger line and Item numbers.

It still however terminates before the end of the data set. Row 38, not Row 39 like it should.

In a test on my larger spreadsheet, it terminates much earlier than expected. I've attached some of this data and your macro.

Example Workbook 130628 2.xlsm

Regards,

Declan

Dave,

Brilliant.

Thankyou,
Declan