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

#### grooveriw

##### New Member
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.
 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>

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

#### footoo

##### Well-known Member
Code:
``````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

##### New Member
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

##### Well-known Member
You need it to look like this:
Code:
``````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

##### New Member
Brilliant, many thanks for your quick response.

#### My Aswer Is This

##### Well-known Member
Brilliant, many thanks for your quick response.

1,082,283
Messages
5,364,268
Members
400,787
Latest member
bs04c

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...