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.

 

[gitmeto]

Is the data input via a form or directly into the sheet?

 

[winsbury]

Is the data input via a form or directly into the sheet?

Everything is via forms to keep users away from entering data directly into tables. Admin is also done via forms hence the need to be able to delete the rows except row 1 using VBA, its very easy manually within excel but just as easy to make an error so really want this done within the scripting.

 

[gitmeto]

Define a named range, write a macro to clear the named range

[namedRange].clearcontents

Do you need to delete the rows or just clear them?

 

[winsbury]

rows must be deleted not just data cleared.

 

[gitmeto]

have you ever recorded a macro before?

 

[winsbury]

yes of course

 

[gitmeto]

Sorry, I never assume. Have your tried to record the macro and see what code is generated?

 

[winsbury]

Sorry, I never assume. Have your tried to record the macro and see what code is generated?

no problem. Yes , recording a macro while selecting and deleting multiple rows in a table simply generates multiple deletes, exactly equivalent to my first code example so just as slow when run in the main code.

 

[gitmeto]

Are there any formulas that will refer to the cells that contain the data that you are looking to delete?