Excel Macro - Loop to copy column of cells into row 2

[jupops]

Good Morning

Could anybody please help. I am trying to write a macro that will concatenate cells or move the row up to the next column of a cell which starts with a number, Then stops when the row is empty. But then moves down the row until the cell starts with a number and repeats the procedure until it the end of the report. I have been trying to write the loop and moving the row (or in the case cell) into the cell that is empty of the cell that starts with a number,

For Example,, All the below are in column A and starts from Row 1

7300002 ALIT 1109 041109
BRANDED LEIS iINFO
THIS ITEM IS SUBJECT
INSPECTION BY JOE

7300003 ALIM 311211
ABCD CHECK
ALL OK

7300003 FRED 2511011211
OPEN WHEN READYY
ABCD NOT CHECKED
SUBJECT TO FFRES
DEK GO TO BAR
INSPECTION BY JAMES



So I tried to copy A1 (7300002 ALIT 1109 041109) into A1 on a new sheet, then because next cell below was not empty (A2 - BRANDED LEIS iINFO) copied into B1 on the new sheet, Then it would move down to the next cell (A3 - THIS ITEM IS SUBJECT) and this would go into C1 on new sheet and repeat so A4 (INSPECTION BY JOE) will be copied to D1 on new sheet. Now because the row is blank it ends the loop and Finds the next populated cell from the first sheet and repeats the operation, but copying to row 2 on new sheet so A6 (7300003 ALIM 311211) copied to A2, A7 (ABCD CHECK) Coped to B2 and A8 (ALL OK) copied to C2. Again with the blank row ends loop.

This is repeated until end of document,

I would be grateful for any guidance. Thank You

Regards

Jupops

 

[SkipVought]

hi,

I am trying to write a macro...[/code]
THEN please repost in forum707, along with the code you have so far.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

If you only need to do it occasionally, this task can be accomplished very easily using formulae, without VBA.

Place in B1 the formula
=A1

Place in B2 the formual
=IF(A2<>"", CONCATENATE(B1,A2), "")
and copy this to the bottom of column B.

Place in C1 the formula:
=IF(A2="",B1,"")
and copy this to the bottom.

This should now give you the concatenated text in this column, with lots of blank rows

Place in D1 the formula:
=IF(C1<>"",ROW(D1),"")
and copy this to the bottom.

Now copy columns C and D, paste-special-value into any spare place, and sort your new two columns by the row number (the value that used to be in column D).

This keeps the entries in their original order, but moves them to the top of the sheet with all the blank lines removed.

 

[jupops]

Sorry Skip. not a good start for my question, I have follewed your link and reposted, cheers.

Jupops

 

[jupops]

Thank you lionelhill this is great thank you.