Can't Insert a Table Row next to a Pivot Table 1

[MattGreer]

I have a table that I'm filling out, a budget for a project, and rather than have to right click and click and click to insert a row, I added a button and VBA to add a table row, and another button to delete a table row.

Sitting to the right of this table is a Pivot Table which summarizes the costs.

When the active cell is in a row next to the pivot table, I cannot add a row. But if I'm below the pivot table, I can. So if the last row of the pivot table is row 12, I have to be in row 13 or greater to use the button.

Here's the code for add and delete. Pretty simple stuff of course:

Sub InsertTableRow()

    Rows(ActiveCell.Row).Insert

End Sub

Sub DeleteTableRow()

    Rows(ActiveCell.Row).EntireRow.Delete

End Sub

Is there a way to insert a row in a table next to a pivot table, independent of where I might be at the time? That's the key of course, I might decide to insert or delete a row anywhere.

Thank you!

Thanks!!


Matt

 

[SkipVought]

Hi,

So the active cell is not in the table (I assume a structured table) in question.

What's the objective?
1) To add a row to the table BELOW the active row?
2) To add a row below the LAST row of the table without regard to the active row?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Anyhow, Here's a solution that works with an adjacent PivotTable, assuming that the Structured Table is the FIRST ListObject on the sheet...

Sub InsertTableRow()
'inserts a row below the row of the ActiveCell
    Dim lLastRow As Long

    With ActiveSheet.ListObjects(1)
        lLastRow = .Range.Rows.Count + .Row - 1
        
        If ActiveCell.Row = lLastRow Then
            .ListRows.Add AlwaysInsert:=True
        Else
            .Range.Rows(ActiveCell.Offset(1).Row).Insert
        End If
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Hiya skip!

The active cell IS in the table. I work on the table, get budget items together, and when I'm done I go update the pivot table to summarize the cost categories together. The table keeps all the details together.

When I'm in the table in a row that doesn't coincide with the pivot table the code works perfectly. To be honest, I'd prefer that the new row be added above the active cell.

On this line: lLastRow = .Range.Rows.Count + .Row - 1

I get the error 'Run Time Error: Object doesn't support this property or method'

This obviously is going to require more than one line of code, so I came up with this. I had to use -6 on the Offset because the table header is on Row 7.

Sub InsertTableRow()
    Dim lLastRow As Long
    Dim tbl As ListObject
    
    Set tbl = ActiveSheet.ListObjects("BudgetTable")
    
    'inserts a row below the row of the ActiveCell
    With tbl
        .Range.Rows(ActiveCell.Offset(-6).Row).Insert
    End With

    Set tbl = Nothing

End Sub

I was wondering about something, a looong time ago I was taught that objects need to get set to Nothing or, well, I dunno, problems? So I've been doing that. Is it needed tho? I don't get an error.


Thanks!!


Matt

 

[SkipVought]

Sorry, my bad.

Try this...

Sub InsertTableRow()
    Dim lLastRow As Long

    With ActiveSheet.ListObjects(1)
        lLastRow = .Range.Rows.Count + .Range.Row - 1
        
        If ActiveCell.Row > lLastRow Then
            .ListRows.Add AlwaysInsert:=True
        Else
            .Range.Rows(ActiveCell.Row - .Range.Row + 1).Insert
        End If
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

So what are your thoughts on deleting a table row? Code I had above didn't work for that either.

Thanks!!


Matt

 

[SkipVought]

Sub DeleteTableRow()
    Dim lLastRow As Long

    With ActiveSheet.ListObjects(1)
        lLastRow = .Range.Rows.Count + .Range.Row - 1
        
        If ActiveCell.Row <= lLastRow Then
            .Range.Rows(ActiveCell.Row - .Range.Row + 1).Delete
        End If
    End With
End Sub

Notice that the [tt].Insert[/tt] has been replaced by [tt].Delete[/tt].

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein