Link to home
Start Free TrialLog in
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
SOLUTION
Avatar of Kimputer
Kimputer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jagwarman
Jagwarman

ASKER

Both Excellent thanks
Haha not both excellent. Hands down for Rgonzo1971's complete and utter efficiency.
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
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

Thanks Rgonzo1971
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
If you could do it it would be nice