Rearrangement of reported data

[JMay]

Monthly I receive an excel file that looks as so:

A B C D E
1 Dept Code Date Amt Comment
2 977 123 4/05/04 100.00 asdf
3 877 234 4/03/04 90.00 sdfg
4 777 345 4/05/04 80.00 fghjgh
5 ABCD
6 877 222 4/03/04 110.00 ytry
7 777 333 4/01/04 75.00 wer
8 666 444 4/05/04 65.00 nbvc
9 555 988 4/06/04 83.00 kjlkj
10 435 922 4/06/04 125.00 lkjklj
11 555 834 4/11/04 111.00 lkj
12 DERB
.............

Above are only Two Blocks of maybe 100 Blocks;
Example Block 1 (above) has 3 lines and Block 2 has 6 lines.
A Block **CAN BE** only 1 line of data
But each block is followed by a code in Column D that applys
to each item in the block Above it.

For starters I have inserted a column Before Col A Above thinking
I could enter in new A2 an Offset() function that could somehow
bring back the "ABCD" Value.. Same in A3 that would bring back
the "ABCD: Value.

After mulling over I haven't got a clue where to begin on this.
Can anyone assist with a plan?
TIA,

 

[Norman Jones]

Hi JMay,

Try:

Sub Tester()

Dim ar As Range
ActiveSheet.Range("A1").EntireColumn.Insert
For Each ar In Columns(2) _
.SpecialCells(xlCellTypeConstants).Areas

ar.Offset(0, -1) = _
ar(ar.Cells.Count).Offset(1, 3)
Next

End Sub

 

[JMay]

Norman:
Thanks for the code; It works great!!
JMay