Auto Group data

[wannabe12]

Hi there,

I have this Dataset -

170</SPAN>	Batch No: 170 Period: 12/13/01/01</SPAN>
170</SPAN>
170</SPAN>	26/04/2012</SPAN>	400</SPAN>	Narrative of Jnl </SPAN>	BC01</SPAN>	D001</SPAN>	3MM12 </SPAN>	-250</SPAN>
170</SPAN>							---------------</SPAN>
170</SPAN>							-250</SPAN>
170</SPAN>
179</SPAN>	Batch No: 179 Period: 12/13/01/01</SPAN>
179</SPAN>
179</SPAN>	18/04/2012</SPAN>	400</SPAN>	Narrative of Jnl </SPAN>	BC01</SPAN>	D001</SPAN>	2IRIMPACT </SPAN>	-50</SPAN>
179</SPAN>	18/04/2012</SPAN>	400</SPAN>	Narrative of Jnl </SPAN>	BC01</SPAN>	D001</SPAN>	2RETWARM1 </SPAN>	-12</SPAN>
179</SPAN>	18/04/2012</SPAN>	400</SPAN>	Narrative of Jnl </SPAN>	BC01</SPAN>	D001</SPAN>	2IRUNSOL </SPAN>	-15</SPAN>
179</SPAN>	18/04/2012</SPAN>	400</SPAN>	Narrative of Jnl </SPAN>	BC01</SPAN>	D001</SPAN>	2IRIMPACT </SPAN>	0</SPAN>
179</SPAN>							---------------</SPAN>
179</SPAN>							-77</SPAN>
179</SPAN>

<TBODY>
</TBODY>

Basically I need a Macro thats groups the data, at each Change in column A. However in simple steps I would like the Macro to do this -
For the first row of "170" to go to column H, and search for -------, the Number below this copy to the first row of 170.
So if the row of the first line of 170 was Row5 and the column with all the ---- in is column H, the macro would copy the "-250" to H5,
on the same line as the title Batch.
Next the Macro will move to the second line of 170, and group this line all the way to the last line of 170.
Next the macro will move to the next batch of 179. Again it will go to column H search for ---, copy the figure one cell below this
which is -77, copy this to the same line as "Batch No:179....", but in column H. So if this row is row11, -77 would
be copied to H11.
Then Group the next line of 179 to the last line of 179, and then move to the next batch.
For info - Im at beginner level on Macros, and anyones who does this I can donate some money to a Charity of your choice or
to Mr Excel.

Kind Regards,
Amish

 

[Snakehips]

Amish,

Try this code. Either in a code module or in the sheet module.
It needs your sheet to be Active when run.
Not sure what you have in the way of headers so edit the red 3 to suit your first line of data !!!!!!!!

Sub Auto_Group()
Dim s As Integer
Dim c As Integer
Dim bnum As Integer
s = 3  '****ie start at row 3 ** edit if start is different row!!
bnum = Cells(s, "A")
Do Until bnum = 0
c = s
Do Until Not Cells(c, "A") = bnum
If Cells(c, "H") Like "*-----*" Then Cells(s, "H") = Cells(c + 1, "H")
c = c + 1
Loop
Range(Cells(s + 1, 1), Cells(c - 1, 1)).Rows.Group
Range(Cells(s + 1, 1), Cells(c - 1, 1)).Rows.EntireRow.Hidden = True
s = c
bnum = Cells(s, "A")
Loop
End Sub

Hope that helps.