VBA: Have Macro to Add "X" Rows - How To Auto-Populate Data in "X" Rows?

redlegos

New Member
Joined
Jan 10, 2017
Messages
3
Hello,

I am creating a worksheet that has a Dropdown list with values in Column B, and depending on the value the user selects it will call the macro below to insert a user defined number of rows below their selection. I then want the macro to populates specific text in Columns A, B, and I for every row created. The other cells can remain blank as they are now.

If user inputs to create 10 rows, then 10 rows are created with each row containing the following text:
Column A: "Sub Line"
Column B: "Internal Component"
Column I: "N/A"

I feel like this shouldn't be complicated but am still learning and reading up on VBA and the whole custom number of rows is throwing me off. Would be much appreciated if someone can point me in the right direction. Thank you!

Code:
Sub CustomRow()
Dim X As Integer
x = Application.InputBox("How many rows?", "How many rows", Type:=1)
If x = False Then Exit Sub
Range(ActiveCell.Offset(1), ActiveCell.Offset(x)).EntireRow.Insert Shift:=xlDown
End Sub
Dim x
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
Code:
Sub CustomRow()
    Dim x As Integer
    x = Application.InputBox("How many rows?", "How many rows", Type:=1)
    If x = False Then Exit Sub
    With ActiveCell.Resize(x)
        .Value = "Sub Line"
        .Offset(, 1).Value = "Internal Component"
        .Offset(, 8).Value = "N/A"
    End With
End Sub
 

redlegos

New Member
Joined
Jan 10, 2017
Messages
3
Ok that was surprisingly fast - thank you. It inserts the values as I requested but now just need them to be inserted down a row as the starting point. Do I need to incorporate the EntireRow shift again?

In Row 1, user selects value and macro is called prompting for number of rows to be added, I want their original values to stay in Row 1 and the macro to add their rows and populate under Row 1.
Currently it starts in Row 1 and overwrites their data.
 

redlegos

New Member
Joined
Jan 10, 2017
Messages
3
Thank you so much again for your assistance! I was able to learn some more and alter it further for my specific purposes. This forum is a great resource.

Code:
Sub CustomRow()
    Dim x As Integer
    x = Application.InputBox("How many rows?", "How many rows", Type:=1)
    If x = False Then Exit Sub
    Range(ActiveCell.Offset(1), ActiveCell.Offset(X)).EntireRow.Insert Shift:=xlDown
    With ActiveCell.Offset(1,0).Resize(x)
        .Value = "Internal Component"
        .Offset(, -1).Value = "Sub Line"
        .Offset(, 7).Value = "N/A"
    End With
End Sub
 

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
hi.gif
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,863
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top