VBA to create report layout from hierarchy

[CROY1985]

Hi all

I have a reporting hierarchy which is laid out like below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]North East[/TD]
[TD]Sunderland[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]North East[/TD]
[TD]Sunderland[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]North East[/TD]
[TD]Newcastle[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]North East[/TD]
[TD]Newcastle[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]North West[/TD]
[TD]Preston[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]North West[/TD]
[TD]Preston[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]


I need a macro to convert it to the below:
A
B
Sunderland Total
C
D
Newcastle Total
North East Total
E
F
Preston Total
North West Total

It's easy in a pivot table but would prefer a vba solution.

Can someone just point me in the right direction for this?

Thanks