The VBA macro recorder is a really wonderful tool, especially when you want to get an insight into an unfamiliar part of the Excel object model. However, it’s notorious for producing inefficient code – and clearing a table is a typical example.

I’ve created a simple table called Table1 in Excel 2010 with four columns.

The first three columns contain constants and Col4 contains this formula:

=[@Col2]+[@Col3]

I want to clear this table as part of my VBA automation code so, to get an idea of the code involved, I do it manually with the macro recorder turned on. I clear the table by selecting all the data in Col1, right-click > Delete > Table Rows. The output looks like this:

The first thing that is evident is that this code isn’t going to work unless there are five rows of data in the table, but we can take the hint from the macro recorder that we’re interested in ListObject.ListRows (in the Excel Object Model a table is a ListObject) and introduce the flexibility we need. While we’re at it we can remove the Selection object too – perhaps ending up with something like this:

Sub Macro2()
With Sheet1.ListObjects("Table1").ListRows
Do While .Count >= 1
.Item(1).Delete
Loop
End With
End Sub

That code is fine and it does exactly what it says on the tin, but you’ll find that if the table is very large then the code will be slow – even if you turn off Application.ScreenUpdating.

The rule of thumb is that, when you’re deleting rows or columns in Excel, it’s much quicker to delete them all in one go and not one at a time – I’ll explain why this is in a future blog post about deleting worksheet rows. The macro recorder has slightly mislead us with its naive ListObject.ListRows property suggestion because we can do this task much more efficiently using the ListObject.DataBodyRange property instead (again, I’m not including optimisations such as setting Application.ScreenUpdating to False):

Sub Macro3()
With Sheet1.ListObjects("Table1")
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With
End Sub

Once the table has been cleared you’ll notice that, if you start to put data back into it, it remembers the formulae in Col4 and automatically puts them back in for you. In the below screenshot I’ve just typed in “a” in Col1.

This behaviour is built into tables and hidden away in their inner workings: as far as I can tell, there isn’t an exposed Application or ListObject property we can use to control it. One property which looks like it might fit the bill is Application.AutoCorrect. AutoFillFormulasInLists, but this setting controls whether or not a formula is filled down the entire ListColumn once it has been entered. Incidentally, why does this setting (along with AutoExpandListRange) need to be at the Application level? It’d be so much more useful if it could be controlled on a table by table basis at the ListObject level.

If you want to stop the table from remembering formulae when you clear it then one way I’ve found is to clear the contents of the data body range before deleting it:

Sub Macro4()
With Sheet1.ListObjects("Table1")
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.ClearContents
.DataBodyRange.Delete
End If
End With
End Sub

It’s true that you could just delete the table entirely and then create a new one, but that may not be a viable option if you have formulae and the such which reference the table.

As far as I can tell, one would always want the formulae reinstated in this situation, Bob. The issue is that Excel does it in an inefficient way.

For example, this feature caused a problem for me in a project where I had a table with (approximately) 5 columns of data and 5 columns of formulae, because I found that the table formulae are all recreated and calculated in one swoop when data is put back in. I was putting in a large dataset and the automation became extremely slow – almost to the point where I thought Excel was giving up the ghost. So I cleared the table as described on the post so that the formulae would not automatically be reinstated, put the data in and then added the formulae back in one column at a time. Doing it this way was extremely fast in comparison.

I agree with you that the behaviour is understandable – and in most cases desirable – and I never said anything to the contrary on my post. What I did say is that I want a listobject property to control this behaviour, as well as listobject level properties which do the equivalent of Application.AutoCorrect.AutoFillFormulasInLists and Application.AutoCorrect.AutoExpandListRange. I want to control these behaviours table by table, not by application.

I ran into an issue with this recently. One particular table I was working on had a variable amount of calculated columns, where throughout the year data would be updated and the data pushed back into the tables. This isn’t a problem so long as the data range didn’t over-step where the formulas were that I wanted to keep.

This did, however, present a problem when rolling into a new year (calculated columns were months, 1-12), as there would be data for a shorter number of months, of which the previous columns calculations were already over-written. In this case formulas were forced to be reinstated afterwards.

In doing testings with tables is when I ran into the fact that table formulas persist. I admit, it is very handy and I’m a big fan, but I deplore not having control over it. Honestly I didn’t know about this a week ago.

In regards to to the application-level (via AutoCorrect object) settings Colin points out, I too agree it should be part of the ListObject object. It makes sense in my head anyway. That being said, AutoFillFormulasInLists doesn’t always stop formulas from filling in tables. The sample code I used was to delete the entire DataBodyRane and add back after setting the property to False. In all cases I couldn’t stop the formulas from auto-filling in, so long as I didn’t 1) overwrite the range, 2) clear the range manually. I can be happy in the fact this is the default behavior, but not having control over it? Ugh.

Probably the biggest heartache when dealing with tables via VBA was the DataBodyRange when deleted, as it returns to Nothing when done. The test works well, and to instantiate it again you can just add a ListRow, which is when the formulas appear.

Hi Colin, I just came across your website because I was looking for a way to clear a table, and this is exactly what I was looking for. There’s a minor cosmetic issue that I was wondering if you had a workaround for. When dealing with a table with color-banded rows, deleting the rows this way and then pasting my new data into the empty table results in a table with non-banded rows and instead all of the rows have the same color as the first band/row. I’m using Excel 2010. Thanks for your help.

The banded row setting shouldn’t change (unless done so manually). I would think there may be formatting applied to the table that is showing. A good way to test this would be to apply the table style and clear the underlying formats. To do so right-click the table style and choose ‘Apply and Clear Formatting’.

The only way I can reproduce this problem is by formatting the cells containing the new data so that they all have the same colour as the first row of the table before I copy and paste them into the empty table. Could you give some step by step instructions so that I can try to reproduce it?

By the way, the above post is very much VBA (automation) specific. If you’re just using the user interface, you can select the table’s rows, right click | delete | delete table rows.

I cleared formatting on the table and now it works normally when I paste it. I inherited this table and I guess for some reason the first row’s fill color was exactly the same as the actual color of the first band. Thanks to both of you for your help!

Hello!! Thanks you very much for the post, I am having a similar problem,
but the point is that i want to clear the content and leave the formula of a row selected by the user.
In resume it would be delete the row and then just drag the formulas from the row above, Could you give me an idea ?
Thank you very much!
Lorena

Not really sure how your data is setup, where your cells references are located, or where you’re using tables here, so it’s difficult to give you a table formula. It appears you are naming ranges, which isn’t bad, but as you’ve found out if you delete the cells then the reference is then invalid. It’s better to setup your data so you don’t delete cells.
If you set your times up as a table, if there are only 3 values, you could do either by columns or by rows. I would probably do by column so you could reference them individually as opposed to doing a lookup to find the row they’re on.
If you have data on both sheets setup in a table it would be easiest. Assuming you had a table on Sheet2 with the following columns:
Task, Start Date, End Date, Days Completed, Duration, Days Left to Complete
On Sheet1 you could have a table and lookup any value based on Task, assuming those would be unique values.
Assuming your table on Sheet2 was named “tblDates”, Sheet1!A1 has a value of a task, Sheet1!B1 has a value of time (we’ll say ‘Duration’, matching a column header on Sheet2), you could have a dynamic formula like this on Sheet1…
=IFERROR(INDEX(tblDates,MATCH(Sheet1!A1,tblDates[Task],0),MATCH(Sheet1!B1,tblDates[#Headers],0)),”Not found”)
This is basically an INDEX() formula with two MATCH() functions, one for the row and one for the column.
Not sure what else to tell you here as there’s very little information to go on. Suffice to say I would not recommend deleting cells, especially if they’re named ranges.
HTH
Zack

Zack, sorry to bother you again with this issue,
But i am trying to build my data using these structured reference, by giving name to the tables but its not working, anyhow
could i send to you my spreadsheet ?

Hi, I’m a beginner with VBA and Excel macros in general, and I have a question, hoping someone can help me resolve:
I have two separate workbooks. A macro in workbook #1 copies data from itself, into a table in workbook #2. The data that is pasted into workbook #2 needs to be appended, that is, pasted into the first empty row. My issue is when the table in workbook #2 is cleared and only the headers remain in row 1, the data is copied starting from row 3, which is outside of the table and therefore the pasted data loses it’s table attributes. I have not figured out how to detect when there is only the header row, to copy my data into row 2 so as to maintain the table attributes for the pasted data. I hope my explanation isn’t confusing. Any help would be appreciated.

Think of it to detect if there is a DataBodyRange instead of headers. You can delete the DataBodyRange so that it is Nothing, even though it will shop up on the worksheet and appear as if it’s there, there is no object instatiated yet. A simple test will check that…

If TABLE.DataBodyRange Is Nothing Then
‘no data body range, insert a row
TABLE.InsertRowRange(1).Insert
End If

When appending data to a table, I always want a new row. As you said, if the data body range has been deleted it needs to basically be the first row of the table. I use a function (below) to always get a blank row regardless…

Function GETNEWTABLEROW(ByRef loLOOK As ListObject) As Long
If loLOOK.DataBodyRange Is Nothing Then
loLOOK.InsertRowRange(1).Insert
GETNEWTABLEROW = 1
Else
If loLOOK.ListRows.Count = 1 Then
If WorksheetFunction.CountA(loLOOK.ListRows(1).Range) > 0 Then
GETNEWTABLEROW = loLOOK.ListRows.Count + 1
loLOOK.ListRows.Add GETNEWTABLEROW
Else
GETNEWTABLEROW = 1
End If
Else
GETNEWTABLEROW = loLOOK.ListRows.Count + 1
loLOOK.ListRows.Add GETNEWTABLEROW
End If
End If
End Function

Holy Jumpin Jupiter!! You solved my problem in a most well written explanation and extremely short code – Sub Marro3() . I have no clue what .DataBodyRange does, nor ListObjects – need to read up BUTTT, I can now clear my Table of input, ready for new input, BUT MOST, MOST IMPORTANTLY, LEAVE THE COMPLEX FORMULA I HAVE IN THE LAST COLUMN. Whew…maybe one of these years I’ll learn this stuff….Thanks very much for sharing – most considerate of you!!

My requirement is to have a “Command Button” to “Reset the Input Data”. Once the user presses the reset button,
a) Un-protect the sheet with a password
b) Erase the data from A4-B4-C4, A5-B5-C5, but do not delete the table rows as it has conditional formating and data validation rules which needs to be copied when the user enters a new investment record
c) Delete all the “Table Rows” from A6 onward
d) Protect the sheet with the same password

Deleting the DataBodyRange will remove all table rows. Instantiating the ListRows object (i.e. adding new rows) should still retain the conditional formatting, so long as you applied it to the table body column.

Also, when you protect the sheet (in the Protect method), make sure you use the parameter UserInterfaceOnly:=True. This will make it so code, from this point on, doesn’t need to unprotect/protect the sheet to work on it. It could be something like this…

Clearing the data body range is fairly simple at this point, which will still retain your conditional formatting. Just like the ‘Macro3’ example Colin posted above, you can just delete the DataBodyRange object if it is not nothing. The only other thing I do is instantiate a row afterwards, like this…

Sub DeleteTableBody()
With ActiveSheet.Range("B5").ListObject
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
.ListRows.Add
End

Ah, you know what, I forgot about the tables issue with worksheet protection. To sort & filter you must have the data body and header row cells unprotected, which I understand is counter intuitive to protection in the first place. For adding rows you have to unprotect, do your action, then re-protect. So you can use the code something like this…

This works perfectly fine. May I request you to tweak the above piece of code a bit? Your code deleted all the data rows in the table and inserts a new empty row at the beginning. This results in data validation and conditional formatting properties to be lost.

But, I am looking for
a) Erase the data from A4-B4-C4, A5-B5-C5, but do not delete the table rows as it has conditional formating and data validation rules which needs to be copied when the user enters a new investment record
b) Delete all the “Table Rows” from A6 onward

A4-B4-C4 ==> Has a different set of conditional formating and data validation
A5-B5-C5 ==> Has a different set of conditional formating and data validation from A4-B4-C4,

The conditional formatting from A6 onwards should be copied from A5-B5-C5.

It shouldn’t remove those things if they were applied to the entire table column body. I’d re-apply them, ensuring you have the table column body selected (hover mouse over top edge of column header cell until you get the down arrow, then click once), then the code should work just fine.

That was extremely useful! I was wondering from where on earth these ‘ghost’ formulas came back, when i already deleting all rows? As soon as i entered data, formulas came back, i had a problem with that. Thank you very much! Your trick of clearing the data body range before deleting it is awsome!