Help with VBA code for adding new rows

[SirSquiddly]

I have a button that adds 'x' amount of rows when desired number is entered. I feel this VBA is close and need help adjusting it. My data starts on row 5 and would like new blank (but containing formulas) added above the existing table. E,g,. pushes rows down.

A run-error '1004' pops up saying "this won't work because it would move cells in a table on your worksheet. I click debug and it highlights "Rng.Insert Shift:=x1Down"

Any help hugely appreciated​

[TABLE="width: 4217"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 4217"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OFF HIRE[/TD]
[TD]OFF HIRE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

​

Code:


Sub AddRows()


Const BaseRow As Long = 5 ' modify to suit


Dim x As String ' InputBox returns text if 'Type' isn't specified
Dim Rng As Range
Dim R As Long


x = InputBox("How many rows would you like to add?", "Insert Rows")
If x = "" Then Exit Sub
R = BaseRow + CInt(x) - 1


Rows(BaseRow).Copy 'Copy BaseRow
'specify range to insert new cells
Set Rng = Range(Cells(BaseRow, 1), Cells(R, 1))
Rng.Insert Shift:=x1Down


' insert the new rows BEFORE BaseRow
' to insert below BaseRow use Rng.Offset(BaseRow - R)
Set Rng = Rng.Offset(BaseRow - R - 1).Resize(Rng.Rows.Count, ActiveSheet.UsedRange.Columns.Count)
Rng.Select
On Error Resume Next
Rng.SpecialCells(xlCellTypeConstants).ClearContents
Application.CutCopyMode = False '
End Sub

 

[tyija1995]

Just checking, does that say x1Down (x one down) or does it say xlDown (x L down) ?
Looks like a 1 but need to check!

 

[SirSquiddly]

yeah its a 1. I'm not sure how to insert VBA window like others do.

 

[tyija1995]

Try use an l instead of a 1 see if that fixes it?

To insert the VBA code window use this syntax:

[.code]
VBA Code goes in between
[./code]

Without the dots

 

[gallen]

definitely should be "xlDown"

 

[SirSquiddly]

I tried a small L, same message. Any other ideas?

Sub AddRows()

    Const BaseRow As Long = 5   ' modify to suit

    Dim x As String             ' InputBox returns text if 'Type' isn't specified
    Dim Rng As Range
    Dim R As Long

    x = InputBox("How many rows would you like to add?", "Insert Rows")
    If x = "" Then Exit Sub
    R = BaseRow + CInt(x) - 1

    Rows(BaseRow).Copy          'Copy BaseRow
    'specify range to insert new cells
    Set Rng = Range(Cells(BaseRow, 1), Cells(R, 1))
    Rng.Insert Shift:=xlDown

    ' insert the new rows BEFORE BaseRow
    ' to insert below BaseRow use Rng.Offset(BaseRow - R)
    Set Rng = Rng.Offset(BaseRow - R - 1).Resize(Rng.Rows.Count, ActiveSheet.UsedRange.Columns.Count)
    Rng.Select
    On Error Resume Next
    Rng.SpecialCells(xlCellTypeConstants).ClearContents
    Application.CutCopyMode = False '
End Sub

 

[tyija1995]

The code is working on my screen (Excel 2016) - how many rows are you adding in the input box?

Working in the sense that it pushes a table of data down from row 5 by number of rows specified.

 

[SirSquiddly]

my table starts from row 4 and is from columns A-Z.

I have tried various numbers in the input box?

 

[tyija1995]

In your initial post you stated your data started on row 5, so now that you say that it starts on row 4 I see the same error you do (when re-testing)
Can you insert a row manually above the table (row 1 is fine) so that the data starts from row 5 - then it should work. Otherwise you'll need to modify the macro
"Const BaseRow As Long = 5" should be changed to "Const BaseRow As Long = 4" If your data starts on row 4.

I hope this helps!

 

[SirSquiddly]

Sorry I am confusing. I meant my table headers are row 4, the data I want to be copied is row 5. I have information/calculations in the above rows.​