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

 

[isaacv22]

Hi Guys,

I'm currently trying to create a command button that would insert rows below a selected cell based on a criteria. For instance, you select a cell in column C and push the command button. An input message will ask you "how many tracks does this conveyor have? 1-12" The amount of rows inserted below would depend on the value you input, but I would like to include the selected cell as part of that count.

SAP Autofill Ver_2.1.xlsm
	A		C			F		H
1	Column1		Column3			Column6		Column8
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
Formula Sheet

Cells with Data Validation
Cell	Allow	Criteria
F4:F25	List	=validation_list

Thanks,

Isaac Vasquez

 

[Fluff]

I would like to include the selected cell as part of that count.

Do you mean that if you select 1 row, then no rows are inserted?

 

[isaacv22]

Do you mean that if you select 1 row, then no rows are inserted?

SAP Autofill Ver_2.1.xlsm
	A		C			F
1	Column1		Multiple Tracks			Column6
2	Conveyor		Track			Master Tag
4	Conveyor 1
5	Conveyor 2
6	Conveyor 3
7	Conveyor 4
8	Conveyor 5
9	Conveyor 6
10	Conveyor 7
11	Conveyor 8
12	Conveyor 9
13	Conveyor 10
Formula Sheet

Cells with Data Validation
Cell	Allow	Criteria
F4:F13	List	=validation_list

To further elabortate, when entering data I would like to be able to have the Multiple Tracks button be able to inert x amount of rows based on a criteria. For instance if I were to select the red highlighted cell and the push the multiple tracks button on top of column C, an input message would ask "how many tracks does this conveyor have? 2-12" If I were to input 3 then it would count the from the selected cell and insert two rows below while auto numbering the rows 1-3. Not sure if this helps?

 

[Fluff]

How about

Sub isaacv()
   Dim Rws As Variant
   
   Rws = InputBox("How many rows do you want?")
   If Rws = "" Then Exit Sub
   If Rws > 1 Then ActiveCell.Offset(1).Resize(Rws - 1).EntireRow.Insert
End Sub

 

[isaacv22]

Works great! How would I go about having it auto number the rows? If not possible then no worries, I really appreciate your help!

 

[Fluff]

Which column?

 

[isaacv22]

Which column?

It'd be column C which is the column I'd have the rows inserted to.

 

[Fluff]

Ok, how about

Sub isaacv()
   Dim Rws As Variant
   
   Rws = InputBox("How many rows do you want?")
   If Rws = "" Then Exit Sub
   If Rws > 1 Then
      ActiveCell.Offset(1).Resize(Rws - 1).EntireRow.Insert
      Range("C" & ActiveCell.Row).Resize(Rws).Value = Evaluate("""Conveyor "" & row(1:" & Rws & ")")
   End If
End Sub

 

[Joe4]

How is this question different than what you asked here?

Is it possible to insert rows based on criteria?

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...

www.mrexcel.com

 

[isaacv22]

Ok, how about

Sub isaacv()
   Dim Rws As Variant
  
   Rws = InputBox("How many rows do you want?")
   If Rws = "" Then Exit Sub
   If Rws > 1 Then
      ActiveCell.Offset(1).Resize(Rws - 1).EntireRow.Insert
      Range("C" & ActiveCell.Row).Resize(Rws).Value = Evaluate("""Conveyor "" & row(1:" & Rws & ")")
   End If
End Sub

This works, thank you very much. Kind of a long shot but would it be possible to have the value of the cells on the left and right copied and pasted down or would I have to create another macro for that?

Thanks!

Isaac Vasquez