Help with VBA code for adding new rows

SirSquiddly

New Member
Joined
Jun 26, 2018
Messages
39
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
OFF HIREOFF HIRE-0

<tbody>
</tbody>

<tbody>
</tbody>



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
 
Last edited:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
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!
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,005

ADVERTISEMENT

definitely should be "xlDown"
 

SirSquiddly

New Member
Joined
Jun 26, 2018
Messages
39
I tried a small L, same message. Any other ideas?

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:=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
 
Last edited:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 26, 2018
Messages
39
my table starts from row 4 and is from columns A-Z.

I have tried various numbers in the input box?
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 26, 2018
Messages
39
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,325
Messages
5,528,023
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top