How can I shift all cells right based on a cell value?

[grooveriw]

I am looking for a way to shift all the cells in a row to the right based on the value in one of the cells in that row as my manually created sample below shows.
In this example, I want to shift the cells right based on the value in column A.
Original Spreadsheet:-

	A	B	C	D
1	0	111111	John	54
2	1	111211	Bob	34
3	2	111212	Fred	2
4	2	111214	Chris	0
5	1	111215	Jane	1
6	1	111220	Joan	17
7	2	121101	Sally	25
8	3	121110	Harry	4
9	2	121113	Henry	9

<tbody>
</tbody>

After:-

	A	B	C	D	E	F	G
1	0	111111	John	54
2		1	111211	Bob	34
3			2	111212	Fred	2
4			2	111214	Chris	0
5		1	111215	Jane	1
6		1	111220	Joan	17
7			2	121101	Sally	25
8				3	121110	Harry	4
9			2	121113	Henry	9

<tbody>
</tbody>

Thank you in advance.
I just know that some genius out there will know how to do it.
many regards
Graham

 

[footoo]

Dim rng As Range, cel As Range
Set rng = Range([A1], Cells(Rows.Count, 1).End(xlUp))
For Each cel In rng
    If cel > 0 Then cel.Resize(, cel).Insert Shift:=xlToRight
Next

 

[grooveriw]

Thanks footoo,
I have tried entering the code you posted but get an error

Compile error:
Invalid outside procedure

Haven't a clue what this means.

What am I doing wrong?

thanks

 

[My Aswer Is This]

You need it to look like this:

Sub My_Sub()
Dim rng As Range, cel As Range
Set rng = Range([A1], Cells(Rows.Count, 1).End(xlUp))
For Each cel In rng
    If cel > 0 Then cel.Resize(, cel).Insert Shift:=xlToRight
Next
End Sub

 

[grooveriw]

Brilliant, many thanks for your quick response.

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Brilliant, many thanks for your quick response.