Is it possible to insert rows based on criteria?

[isaacv22]

Hello,

I'm currently looking to see if it's possible to have a spreadsheet automatically insert (x) rows below based on answering a question?

For instances while filling a cell it'll ask me if this a multi track conveyor y or n?

If Y then it'd ask how many tracks? I'd be able to input a number and it'd add that many rows below.

Is this possible?

Thanks,

Isaac Vasquez

 

[Marc L]

Hello,​

​

yes but depending on the worksheet design …​

 

[isaacv22]

Could you elaborate on that?

 

[Joe4]

We need more details on your structure.
Are these questions just cell entries, or are you envisioning pop-up questions?

If cell entries, what columns are these values being entered into?
If pop-up questions, what determines what row to insert these new rows beneath?

 

[isaacv22]

SAP Autofill Ver_2.1.xlsm
	A		C			F		H
1
2	Conveyor		Track			Master Tag		Track Length
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Formula Sheet

So I would like to have the pop up question show up after filling a cell in the conveyor column. Which would in turn change the track column based on my answers to the question.

 

[Joe4]

How exactly would it be changing the "Track" column?
I thought you just wanted rows inserted under that cell?

Remember, while the issue is familiar to you, all we have to go on is the information that you provide.
So be as descriptive as possible. Maybe even walk us through an example with data.

 

[isaacv22]

So to simplify, when I select a cell in the track column Id like a pop-up question asking if there a multiple tracks (y or n). If yes, then a pop up list would show up ranging from 2-12. Based on the number you select it'd automatically insert cells below corresponding to the number selected.

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim chk
    Dim tracks As Long
    
'   Exit if more than one cell selected
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if selection not made to column A after row 2
    If Target.Column <> 1 Or Target.Row < 3 Then Exit Sub

'   Prompt user for how many tracks to insert
    chk = MsgBox("Are there multiple tracks?", vbYesNo)
    If chk = vbYes Then
        On Error GoTo err_chk
        tracks = InputBox("How many tracks are there (2-12)?")
        On Error GoTo 0
        If tracks >= 2 And tracks <= 12 Then
'           Insert rows
            Application.EnableEvents = False
            Rows(Target.Row + 1 & ":" & Target.Row + tracks).Insert
            Application.EnableEvents = True
        Else
            MsgBox "You have not entered in a valid number of tracks.", vbOKOnly, "ENTRY ERROR!"
        End If
        
    End If

    Exit Sub
    
err_chk:
    MsgBox "You have not entered a valid number.", vbOKOnly, "ENTRY ERROR!"
    
End Sub

This code should run automatically whenever you select a cell in column A below row 2.

 

[isaacv22]

Joe,

Thank you very much, worked out very well. I do have a a slight issue. When inserting cells it's not copying formulas on the newly inserted rows throughout the sheet. Would it be possible to just add rows in the to columns A & C for the x amount of tracks?

 

[Joe4]

Joe,

Thank you very much, worked out very well. I do have a a slight issue. When inserting cells it's not copying formulas on the newly inserted rows throughout the sheet. Would it be possible to just add rows in the to columns A & C for the x amount of tracks?

I am not quite sure I completely understand what you are saying.
If you set this up as a Data Table, when you insert new rows into the middle of that table, it should automatically copy those formulas down.