Copy & Paste a Table Row that contains Excel Formulas using VBA

[winsbury]

I have a large and complex table in excel, some of the cells in the first row contains formulas including some array formulas.

Users can input data into rows 2 and below via forms to avoid overwriting the formulas, the formulas fill a number of the cells automatically based on lookups etc from the users data input.

Periodically I want to delete all the user data and only retain the first row.

I have tried a number of different methods but the only one which works is extremely slow:

    Dim oSheetName As Worksheet
    Dim sTableName As String
    sTableName = "MyTable"
    Set oSheetName = Sheets("MySheetName")

    FirstRecord = 2
    LastRecord = loTable.DataBodyRange.Rows.Count
    
    For i = LastRecord To FirstRecord Step -1
        loTable.ListRows(i).Delete
    Next

A much faster method of deleting the entire table contents is

loTable.DataBodyRange.Rows.Delete

In order to use this method I must copy the content of row 1 so that it can be pasted back again after the deletion.

I have tried a variety of different solutions, none of which work well, the nearest I have doesn't preserve the formulas.

    Dim MyRow As Variant
    MyRow = loTable.DataBodyRange.Rows(1)
    loTable.DataBodyRange.Rows.Delete
    loTable.ListRows.Add
    loTable.ListRows(loTable.ListRows.Count).Range.Resize(UBound(MyRow, 1)).Value = MyRow        'Only pastes values, formulas are lost !!!

Any other ideas I could try ?

Thanks in advance.

 

[winsbury]

no, the columns are sumifs to avoid cascading references.

The key element of my question is how to copy the entirety of a table row including data and formulas within vba while processing the delete all rows in between.

 

[winsbury]

Just found this link to examples , ResetTable() looks like it may do what I need, its nearly 4am here so will have to wait till I get back in the office to try it but any other ideas gratefully received in the meantime.

 

[MARK858]

Maybe the below if you are trying to delete all the rows in the DataBodyRange bar the first row as per post 1.

Sub DeleteTableRows()
Application.ScreenUpdating = False
    With Sheets("MySheetName").ListObjects("MyTable")
        .DataBodyRange.Offset(1, 0).Resize(.DataBodyRange.Rows.Count - 1, _
                                .DataBodyRange.Columns.Count).Rows.Delete
    End With
End Sub

Edit: and yes ResetTable() would work except you need to remove the clearcontents line

 

[winsbury]

Maybe the below if you are trying to delete all the rows in the DataBodyRange bar the first row as per post 1.

Sub DeleteTableRows()
Application.ScreenUpdating = False
    With Sheets("MySheetName").ListObjects("MyTable")
        .DataBodyRange.Offset(1, 0).Resize(.DataBodyRange.Rows.Count - 1, _
                                .DataBodyRange.Columns.Count).Rows.Delete
    End With
End Sub

Edit: and yes ResetTable() would work except you need to remove the clearcontents line

brill, looks like we may have a winner, I'll try it out and measure performance before getting too excited though.

 

[gitmeto]

I was just thinking. Could you keep it simple and just do the following

Worksheets("Sheet1").Range("B2:Z44").Rows(100).Delete

Just define a number of rows from your range from the user input.

But I think if the results of the formulas are going to be calculated from the values in the cells within the range, once you delete the rows, no matter what method you use, I would imagine it would give you a "#REF!" as a result in your formulas. This is why I suggested just clearing the cells because it would retain the links to the cells in the formulas.

 

[winsbury]

Maybe the below if you are trying to delete all the rows in the DataBodyRange bar the first row as per post 1.

Sub DeleteTableRows()
Application.ScreenUpdating = False
    With Sheets("MySheetName").ListObjects("MyTable")
        .DataBodyRange.Offset(1, 0).Resize(.DataBodyRange.Rows.Count - 1, _
                                .DataBodyRange.Columns.Count).Rows.Delete
    End With
End Sub

Edit: and yes ResetTable() would work except you need to remove the clearcontents line

Works like a charm, fast too !!

 

[MARK858]

You're welcome

 

[winsbury]

You're welcome

I've adapted it slightly to allow for tables that are already cleared or have zero rows and so that variable numbers of rows can be preserved instead of just the first one, this allows for some static records to stay at the top of a table, hopefully useful for someone else too:

Sub Reset_Table()

'Deletes all rows from the row number value in FirstRecord onward

    If FirstRecord < 2 Then Exit Sub                            ' dont delete 1st row
    If loTable.ListRows.Count < 2 Then Exit Sub          ' never delete first row of table to preserve any formulas and formatting

        With loTable
            .DataBodyRange.Offset((FirstRecord - 1), 0).Resize(.DataBodyRange.Rows.Count - (FirstRecord - 1), _
                                    .DataBodyRange.Columns.Count).Rows.Delete
        End With

End Sub