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:
Sub Macro1() ' ' Macro1 Macro ' ' Range("Table1[Col1]").Select Selection.ListObject.ListRows(1).Delete Selection.ListObject.ListRows(1).Delete Selection.ListObject.ListRows(1).Delete Selection.ListObject.ListRows(1).Delete Selection.ListObject.ListRows(1).Delete End Sub
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.
Pingback: Hidden Treasures – Best of Excel on the Web (Sep 2012) | Launch Excel
Thanks, very usefull for me
LikeLiked by 1 person
This seems like a perfectly understandable and desired behaviour, the table behaves like a table. Even if you clear the data, you have defined attributes of it.
That aside, I am confused Colin. If you want to maintain the table, hence you don’t delete it, why would you not want the formulae reinstated?
LikeLike
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
andApplication.AutoCorrect.AutoExpandListRange
. I want to control these behaviours table by table, not by application.LikeLiked by 1 person
Pingback: Retrieve data from recordset into ListObject
Pingback: Working with tables in VBA « Excel and Access Blog
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.
Some examples I’ve been working on the last couple of days:
http://excelandaccess.wordpress.com/2013/01/07/working-with-tables-in-vba/
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.
Regards,
Zack Barresse
LikeLike
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.
LikeLike
Hi Wayne Tam,
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’.
Zack
LikeLike
Hi Wayne,
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.
LikeLike
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!
LikeLike
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
LikeLike
Lorena, if you use structured references they will always update. Can you tell us what formulas you have and an explanation of the ranges you’re using?
Zack
LikeLike
Hello Zack,
My formula is very simple.
On Sheet 1, i have for a task:
start date …and…. finish date
Sheet 2 will calculate:
Days completed = today – start date
Duration = finish date – start date
Days left to complete = Duration – days completed.
So on sheet 1 i made a link to sheet 2 to see these parameters.
So on Sheet 1 i have a column for these 3 parameters with this statement.
= if (sheet2!duration>0; duration; 0)
So if a person delete this row … it will loose the reference…
Thanks in advance i will try to check structured references =)
LikeLike
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
LikeLike
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 ?
LikeLike
Zack Barresse has written an excellent article on how Excel remembers table formulas:
http://excelandaccess.wordpress.com/2013/07/27/how-do-excel-tables-remember-formulas/
LikeLiked by 1 person
Nice blog. Tnx
I use:
this one is extremely quick
LikeLiked by 1 person
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.
LikeLike
Hi Francisco,
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
Then I use it like this…
Dim NewRow As Long
NewRow = GETNEWTABLEROW(TABLE)
TABLE.DataBodyRange(NewRow, 1).Value = “New row of data goes here”
Is this what you’re looking for?
LikeLiked by 1 person
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!!
LikeLiked by 1 person
Hi,
I have a Excel Table in which the header is in Cells A3-B3-C3 and the data “may be” from A4-B4-C4, A5-B5-C5, A6-B6-C6 and so on and so forth based on the number of input records (At max can be 180)
Header A3 – Dummy Column (Used for intermediate calculations)
Header B3 – Investment Date
Header C3 – Investment Amount
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
Something that I have tried but is not working
ub Macro1()
ActiveSheet.Unprotect
Range(“B6:C8”).Select
Selection.ListObject.ListRows(3).Delete
Selection.ListObject.ListRows(3).Delete
Selection.ListObject.ListRows(3).Delete
Range(“Table1[[Investment ” & Chr(10) & “Date ” & Chr(10) & “(mm/dd/yyyy)]:[InvestmentAmount]]”).Select
Selection.ClearContents
Range(“C5”).Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub
LikeLiked by 1 person
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
LikeLiked by 1 person
Hi,
Thanks, for your response. But the above code is not working.
VB is throwing an error – Run time error: ‘1004’
“Delete method of Range class failed” for the “.DataBodyRange.Delete” line of code.
Please help/guide
LikeLiked by 1 person
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…
LikeLiked by 1 person
Thanks, zbarresse
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.
Thanks,
Truptesh
LikeLiked by 1 person
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.
LikeLike
Pingback: Best of Excel on the Web – Hidden Treasures (2016)
Pingback: Best of Excel on the Web | Candid Sourcing
Pingback: Best of Excel on the Web – Candid Sourcing
Pingback: Hidden Treasures – Best of Excel on the Web (Sep 2012) – Launch Excel
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!
LikeLiked by 1 person