CODEWITHSUNDEEP
excelvba
Frankly Dear
Frankly Dear

Reputation: 1

Run-time Error 1004 while adding table rows

I am trying to use the following code to add rows to a table. I need the number of rows to be dynamic because I am adding rows from one table to my main table, and the number of rows in the supplementary table can vary.

Currently, cell F2 counts the number of rows in the supplementary table, and this is supposed to be how many rows the macro adds to my main table. However, when referencing the RowNumber variable, I receive the error message "Insert method of range class failed".

I tried replacing the variable with different numbers, and then the code runs fine. However, whenever I use the variable, I get the error message.

Appreciate any help.

Sub AddRows()

Dim ws As Worksheet
Dim RowNumber As Range

Set ws = ThisWorkbook.ActiveSheet

    With ws
        Set RowNumber = .Range("F2")
        Range("A8").End(xlDown).Select
        ActiveCell.EntireRow.Resize(RowNumber.Value).Insert Shift:=xlDown
    End With

Range("tblDetail").Select
Range("A10").Activate
Selection.FillDown


End Sub

EDIT: I think I am receiving the error because I have a table which starts three rows below my main table (this is an import file for online accounting software, which requires a certain number of tables). I played around with the value in F2: anything greater than "4" in F2 results in the 1004 error (assuming the active cell when the macro is run is in the last row of my main table). Anything less than 4, and the code runs fine.

Is there maybe a way to use variables with the listrows.add function, since my main table is a list object?

Upvotes: 0

Views: 879

Answers (1)

StoneGiant
StoneGiant

Reputation: 1497

Try This:

ActiveCell.EntireRow.Resize(CInt(RowNumber.Value)).Insert Shift:=xlDown

I was able to reproduce your error in my own spreadsheet. Range("F2").Value was returning a string value when F2 contained the formula =ROWS(Table1). The conversion fixed it for me.

Revised Answer

The following code will append the number of rows specified in F2 to the table in which A8 resides:

Dim insertIndex As Integer
Dim insertPosition As Integer

With Range("A8")
    insertPosition = .Row - .ListObject.Range.Row
    With .ListObject.ListRows
        For insertIndex = 1 To CInt(Range("F2").Value)
            .Add
        Next
    End With
End With

That works even if there is a table immediately below the table in which A8 resides.

Upvotes: 0

Related Questions