Avatar of Jagwarman
Jagwarman
 asked on

insert a single blank row or multiple blank rows with VBA

Can an expert provide the code that will allow the user to insert one or many blank rows.

I am using the below code that works fine for a single row so maybe this code could be amended.

The user needs to say where they want to add the row and how many rows to insert.

theRow = InputBox("Enter Row Number where new row is to be inserted ")

    Rows(theRow - 1 & ":" & theRow - 1).Copy
    Rows(theRow & ":" & theRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Application.CutCopyMode = False
    Cells(theRow, 2).ClearContents
    Cells(theRow, 2).Select

Many thanks
Microsoft Excel

Avatar of undefined
Last Comment
Rgonzo1971

8/22/2022 - Mon
SOLUTION
Kimputer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Rgonzo1971

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jagwarman

ASKER
Both Excellent thanks
Kimputer

Haha not both excellent. Hands down for Rgonzo1971's complete and utter efficiency.
Jagwarman

ASKER
rgonzo1971 I should have said I need to copy the formulas down that are in cells in 'P' can you do this or should I post a new post
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Rgonzo1971

If the formula has no relatve reference to cells above or under it you could use

Sub test()

theRow = InputBox("Enter Row Number where new row is to be inserted ")
Count = InputBox("how many rows ?")

If IsNumeric(Count) And IsNumeric(theRow) And Count > 0 And theRow > 0 And theRow <= Rows.Count Then
    strFormulaR1C1 = Cells(theRow, "P").FormulaR1C1
    Cells(theRow, 1).Resize(Count).EntireRow.Insert
    Cells(theRow, "P").Resize(Count).FormulaR1C1 = strFormulaR1C1
Else
    MsgBox "You didn't enter a valid number!"
End If

End Sub

Open in new window

Jagwarman

ASKER
Thanks Rgonzo1971
Jagwarman

ASKER
Rgonzo1971

I need a variation on your insert rows. I am happy to post a new question if you prefer.

I need to insert row[s] but only in columns A through AM I do not want to insert an entire row.

Thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rgonzo1971

If you could do it it would be nice