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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
hi.gif
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top