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:
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
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
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
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
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.