Solved Formula to insert row based on input

January 8, 2014 at 06:42:48
Specs: Windows 7
I have an excel sheet where I need to insert a row based on the input value (by the user) in a specific column. If the user types 1 in column D, then everything stays the same. If user types 2 in column D, then a row needs to be inserted right below it. If user types 3, then 2 rows need to be inserted below. And finally, if user types in 4, then 3 rows would need to be added. I don't know if it matters, but the input cells start at D9 and keep going down (D10, D11, etc). I also want to make sure that if I type N/A or X in the column, that it will not mess anything up. Thanks ahead of time!

See More: Formula to insert row based on input

Report •

January 8, 2014 at 08:40:49
✔ Best Answer
You can't do what you want with a formula. Formulas can not make "physical changes" to a sheet such as inserting rows, etc. They can only calculate a value in the cell in which they reside. You need to use a macro to accomplish your goal.

Right click on the Sheet tab for the sheet you want this to happen in. Select View Code and paste the following code into the module that opens. Then go back into that same sheet and enter values in Column D.

Note: In order for the macro to work, each user has to have macros enabled on their own workstation. You can not turn on macros on a user's machine with any type of code since that would be a major security issue. You, or the users, or someone from IT has to make sure that macros are enabled and that needs to be done in accordance with your company's IT Security policy.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if input is in Column D, Row 9 or higher
  If Target.Column = 4 And Target.Row > 8 Then
'Check if input was 2, 3 or 4
    If Target > 1 And Target < 5 Then
''If the above conditions are met, Insert Rows
'Turn off Events so code doesn't refire with each insert
     Application.EnableEvents = False
'Insert 1 less row than value entered by user
       For newRw = Target.Value To 2 Step -1
         Rows(Target.Row + 1).EntireRow.Insert Shift:=xlDown
    End If
  End If
'Turn Events back on
     Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 8, 2014 at 08:58:11
Thank you! Is there a way that I can reverse the row insert. For example, I put a 2 and it added the row, but when I change it to 0, the added row stayed. Is there a way I can add something to remove the row based on the number? Also, when I put a 2 in the cell, it adds one row, but then when I change it to a 3, 2 more rows are added on top of the additional row, when I only need 2 rows. Can that be done? Thanks again.

Report •

January 9, 2014 at 13:52:51
You have taken what was a fairly simple request and made it considerably more complex.

VBA has not evolved to a point where it can read minds. As orginally requested, and therefore as written, when you enter a number in a cell, the code inserts one less row than the number entered. Now let's say you come along later and change that number. How is the code supposed to know if you want to insert more rows or delete some rows?

You'll have to tell me if there is something that the code can "test" to determine what action to take. For example, let's say you enter a 3 in D10 and the code inserts 2 blank rows below it. D11:D12 will be empty. If you immediately enter a 2 (meaning you want 1 less row than previously added) the code can check for the empty cells, delete them and then add a single row.

However, if you enter a 3 in D10 and thne enter something in D11 and then change D10 from a 3 to a 2, how will code know what you want to do? It can't test for empty cells so it will have no way of knowing whether you want to add or delete rows.

We need to know more about you are trying to do - in it's entirety - before we can offer any more suggestions. If you add requirements in small pieces, it is very hard to write code because it's not just a matter of adding more instructions. Sometimes it means a rewrite of everything. That gets fustrating, so it's better if you tell us everything you need done upfront.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question