How Macro to append entire rows until next new "key" row

[JonJeffrey]

Hi all,

Assitance appreciated

I have a set of data with a value in column A (A1) and many other columns of data in the first row,
followed by any number of rows with no data in the A column, but with many other columns populated.

This pattern then repeats.

I wish to append any following rows to each row that has a value in Column A, until starting a new row when next encountering a value in Columns A and the appending following ...etc

(Note the value in A is not unique)

Eg (With Commas representing columns)
Jon,1,2,3
,a,b,c,d
,4,5,6,7,8,9,10,11,12
Jon,x,x,x,x
,1,2,3,4
,w,w,w,w

to become

Jon,1,2,3, ,a,b,c,d, ,4,5,6,7,8,9,10,11,12
Jon,x,x,x,x, ,1,2,3,4, ,w,w,w,w

[MarvinP]

Hi JonJeffrey and welcome to the forum.

Here is the VBA code that will do as you suggest.

Please Login or Register  to view this content.

Find the attached with your example.

[JonJeffrey]

Thanks MarvinP for the prompt reply.
It doesnt function correctly as it stands. I'm looking at this macro to see if I can debug it.
Have sent details privately.

[MarvinP]

Hi Jon,
There is no need to send private info that isn't private.
You said that B2 and C2 may be blank so my code needs to pick this up.

This is kind of a new problem. I'll diddle with my code too and see if I can trim off the left side blanks.

Here - Try this one

Please Login or Register  to view this content.

I added a FirstCol to account for the left few cells being blank
Hope this works for you.

If not then attach a sample workbook so I don't have to guess at what the data looks like.

Click on the Go Advanced and then the Paper Clip Icon to attach a file.

[JonJeffrey]

Hi Marvin P,

Many thanks for your responses. I'm attaching a small sample.
Its really just about appending all the data in the interim "blank" A rows to their respective parent (previous) header row.

Regards Jon

[MarvinP]

Hey Jon,

I didn't think you had any entirely blank rows in your data. Fooled me!
Try this third attempt to see if it does what you want.

Please Login or Register  to view this content.

[JonJeffrey]

Now this looks like Gold :-)
I'll have a play & post again later.
Thanks very much for your time
Regards Jon

Yes thanks solved :-)