 # Insert row based on cell value

• My current code works, but there's got to be a shorter version to insert rows based on a cell value. Currently my code works on a series of If statements. If the value in the current cell is "2" then goto the next row and insert one line. If the value is "3" then go to the next row, insert, next row insert etc. I'm currently written up to a value of 10, but the coding is getting longer and longer. Anybody got a shorter loop that I could use.

Thanks! :thanx:

• I'm a bit unclear as to exactly where you want the lines inserted, but try the following and adjust as needed.
[vba]
Sub InsertSome()
Dim i As Integer, n As Integer, m As Long
n = ActiveCell.Value - 1
m = ActiveCell.Row - 1
For i = 1 To n
Rows(m + 2 * i).Insert
Next i
End Sub
[/vba]

• If I reade the original post correctly, you want the code to insert the number of rows indicated in the cell value (ie if it is 1 then insert 1 row, if 2, then 2 rows, etc)

If this is the case, then I believe the Derk's code should be modified as follows:

Code
``````Sub InsertSome()
Dim i As Integer, n As Integer, m As Long
n = ActiveCell.Value
m = ActiveCell.Row
For i = 1 To n
Rows(m + 1 * i).Insert
Next i
End Sub``````

This will insert the rows directly below the active cell.

• :? I didn't explain this well the first time, so I'll try to clarify this time:

I have XXX rows of customer data. I have the spreadsheet calculate the number of possible customer options based on a simple count of the columns to the right of the current cell. That count value is then pasted a value into the same column.

What I'm currently doing is running a macro that looks at the value in the current cell and IF it's greater then 1 then insert a row below it. After it inserts the row it goes to the next cell and insert row etc. This worked fine as long as the customer options were low values, but currently I'm up over 40 possible combinations.

I've modified the current code you guys provided hoping it would do the same thing, but it keeps adding the rows at the first record row instead of the current record row.

*sigh* Where the heck did I go wrong???? :?

Original code

Modified code

• See if the following works for you.
[vba]
Sub TryThis()
Dim i As Integer, n As Integer, m As Long, currentCell As Range
Set currentCell = ActiveCell
Do While Not IsEmpty(currentCell)
n = currentCell.Value - 1
m = currentCell.Row
If n > 0 Then
Rows(m + 1 & ":" & m + n).Insert
Set currentCell = currentCell.Offset(n + 1, 0)
Else
Set currentCell = currentCell.Offset(1, 0)
End If
Loop
End Sub
[/vba]

• WOO HOO!!!! This forum rocks! :rock: I knew there had to be a simplier way to do what I wanted, but apparently I'm having a serious silver moments. :roll: This solution works perfectly.

THANK YOU, THANK YOU, THANK YOU!!!

:thanx:

• Re: Insert row based on cell value

the third line given by Mr.Derk should be

n = ActiveCell.column - 1
n = ActiveCell.Value - 1

• Re: Insert row based on cell value

hello! I just saw this thread. This is also kind of like the problem I am facing.

This is my coding. It does not do wat i want. I want to check if the cell value is 6922 or 6921. if yes insert 3 rows if not do nothing. Kindly advice me in this topic.

• Re: Insert row based on cell value

Try
[vba]Sub Arrage_Data()
Dim CurrentCell As Range
Dim s As Integer, e As Long, c As Integer
Range("A4").Select
Set CurrentCell = ActiveCell
Do While Not IsEmpty(CurrentCell)
s = CurrentCell.Value
e = CurrentCell.Row
c = CurrentCell.Column
If s = 6922 Or s = 6921 Then
Rows(e + 1 & ":" & e + 3).Insert
Set CurrentCell = CurrentCell.Offset(4, 0)
Else
Set CurrentCell = CurrentCell.Offset(1, 0)
End If
Loop
End Sub[/vba]

• Re: Insert row based on cell value

One more qns, If I had to copy the autofill the first line to the fourth line, 5th line to 8th line, 9th to 12th line and so on all the way to 1000+ what should i do? I had a codding work out till 20++ then i give up because it will let me to my grave copy and pasting. Please provide me with ideas. Thanks!

• Re: Insert row based on cell value

One more qns, If I had to copy the autofill the first line to the fourth line, 5th line to 8th line, 9th to 12th line and so on all the way to 1000+ what should i do? I had a codding work out till 20++ then i give up because it will let me to my grave copy and pasting. Please provide me with ideas. Thanks! Sorry For this extra post. But I dunno how to delete my post. Sorry

• Re: Insert row based on cell value

hi guys! I manage to do the code, but there is some error due to range, can anyone help me? This is the result and the error is commented.

• Re: Insert row based on cell value

I'm not clear as to what you want. Combining the earlier question with your code, o\perhaps it's something like
[vba]Sub CopyDown()
Dim a, b, c, d As Integer

a = 1
b = 1
d = 4

Do While a < 71

Range(Cells(a, b), Cells(a, d)).AutoFill Destination:=Range(Cells(a, b), Cells(a + 3, d)), Type:=xlFillCopy

a = a + 4
Loop
End Sub[/vba]
If this is not what you want, can you be more specific, or better, attach a sample workbook illustrating the before and after situation?

• Re: Insert row based on cell value

Thanks for the reply. It came out as want i wanted. Thank you! ## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!