Sub ABC()
Dim rng as Range, cell as Range, ar as Range
set rng = Columns(1).SpecialCells(xlConstants)
for each ar in rng.Areas
for each cell in ar
if cell.row < ar(1).Row then
cell.offset(0,1).Value = ar(1).Value
end if
next cell
ar(1).ClearContents
next ar
End Sub


--
Regards,
Tom Ogilvy

"T De Villiers"
wrote in message
news:T.De.Villiers.2bq1ns_1154201108.0909@excelfor um-nospam.com...

I have data in following format (this is just an extract, there are 600
rows
under headings A-F)

A
J1
J2
J3

B
J77
J65
J31

C
J22
J11
J9

.......
So I have cost Centres, A -F ( these can be in any order), with Job
numbers
below(always prefixed by J), there can be any number of Job numbers.




I need to get the above into the order:


J1 A
J2 A
J3 A


J77 B
J65 B
J31 B


J22 C
J11 C
J9 C

Many Thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=566317