Solved Macro Or formula to insert x number of rows based on x input

March 20, 2017 at 06:06:41
Specs: Windows 7
Hello, hopefully someone can help me with this. On my current sheet I am trying to find out a way to drive insertion of new rows based of off an input value. For example if I type QTY. 5 in cell A5, 5 rows should be created below it. It would be amazing if someone had a solution!

See More: Macro Or formula to insert x number of rows based on x input

Reply ↓  Report •


✔ Best Answer
March 20, 2017 at 11:07:55
This code will check for 3 things:

Was a change made to Column A?
If yes, was the change made to a single cell?
If yes, was the change the entry of a number?

If all three of those are true, the code will insert rows based on the number entered.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if a number was entered in a sungle cell in Column A
  If Target.Column = 1 Then
    If Target.Cells.Count = 1 Then
      If IsNumeric(Target) Then
'If yes, then disable Events and insert rows
On Error GoTo done
       Application.EnableEvents = False
        Range(Cells(Target.Row + 1, 1), _
              Cells(Target.Row + Target, 1)).EntireRow.Insert shift:=xlDown
'Re-enable Events
      End If
    End If
  End If
done:
 Application.EnableEvents = True
End Sub

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



#1
March 20, 2017 at 06:56:18
This should do what you have asked for:

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine is cahnge was made to A5 only
  If Target.Cells.Count = 1 Then
   If Target.Address = "$A$5" Then
'If yes, then disable Events and insert rows
      Application.EnableEvents = False
        Range("A6:A" & 5 + Target.Value).EntireRow.Insert shift:=xlDown
'Re-enable Events
      Application.EnableEvents = True
   End If
  End If
End Sub

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


Reply ↓  Report •

#2
March 20, 2017 at 07:08:03
I seem to be having some trouble to get this working. I right clicked on the page tab and went to code then pasted the code. Am I supposed to type in 5 or QTY.5 in cell A5? Is there something I am doing wrong? Thanks for your help.

Reply ↓  Report •

#3
March 20, 2017 at 07:25:12
For the code to work you would need to enter 5 in A5.

Perhaps you should explain your process in a little more detail so we can fully understand what you are trying to do.

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


Reply ↓  Report •

Related Solutions

#4
March 20, 2017 at 07:35:31
Yes, sorry about that let me try to explain it a little better. For this sheet I am listing assemblies in the A column. For each assembly there are a certain number of parts. What I asking is if there is a way that I can type in the number of parts in the assembly and create that number of rows to appear underneath. As of now I have been manually adding rows, but I am looking for a way to automate it off of an input number. For the sheet I have multiple assemblies each of which contain multiple parts all listed in the A column. I apologize if this is still confusing its kind of difficult to explain.

Reply ↓  Report •

#5
March 20, 2017 at 10:28:53
OK, you've added a little more complexity than your original "A5" based question. What you want can be done, but we need a few more details.

First: The task will be much easier if the insertion of rows is based a number, not a text string. e.g. 7 is lot easier to deal with than Qty.7 Unless you can guarantee that the text string will always follow a set pattern, the code may have trouble extracting the value needed to insert the rows. For example if the string is always "Qty." with the number following the ".", then we can write code to look for that. However, here is where issues could occur:

In your OP, you used this string: 'QTY. 5". Note the space after the ".". In Response #2 you used "QTY.5". No space. that is the type of thing that can cause errors.

Second: After the rows are inserted based on the number entered in Column A, will you be entering values in those blank cells in Column A? The reason I ask is this: The code is going to monitor Column A for changes. When it sees a change to Column A, it is going to look for a number to use (or extract) and then insert rows based on that number. If you are going to be entering data in Column A that should not be used to insert rows, we have to let the code know that. In other words, the code can't think and it certainly can't read the user's mind, so it has to be told how to recognize what data to use to insert rows and what data to ignore. What can you tell us about what will happen in Column A once the Rows are inserted?

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


Reply ↓  Report •

#6
March 20, 2017 at 10:40:19
So for the first question a number by itself will be sufficient e.g. 7 instead of Qty. 7. For the second question I will need to type text into those generated rows. Once the rows are inserted I will be typing the name of the part in the new cell. If it is easier I can leave the A column empty except the original number input and write the part names in the B column instead. Thanks!

Reply ↓  Report •

#7
March 20, 2017 at 11:07:55
✔ Best Answer
This code will check for 3 things:

Was a change made to Column A?
If yes, was the change made to a single cell?
If yes, was the change the entry of a number?

If all three of those are true, the code will insert rows based on the number entered.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if a number was entered in a sungle cell in Column A
  If Target.Column = 1 Then
    If Target.Cells.Count = 1 Then
      If IsNumeric(Target) Then
'If yes, then disable Events and insert rows
On Error GoTo done
       Application.EnableEvents = False
        Range(Cells(Target.Row + 1, 1), _
              Cells(Target.Row + Target, 1)).EntireRow.Insert shift:=xlDown
'Re-enable Events
      End If
    End If
  End If
done:
 Application.EnableEvents = True
End Sub

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


Reply ↓  Report •

Ask Question