Clearing Excel tables

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.