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.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , , . Bookmark the permalink.

31 Responses to Clearing Excel tables

  1. Pingback: Hidden Treasures – Best of Excel on the Web (Sep 2012) | Launch Excel

  2. Réjean Bilodeau says:

    Thanks, very usefull for me

    Like

  3. Bob Phillips says:

    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?

    Like

    • Colin Legg says:

      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.

      Like

  4. Pingback: Retrieve data from recordset into ListObject

  5. Pingback: Working with tables in VBA « Excel and Access Blog

  6. zbarresse says:

    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

    Like

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

    Like

    • zbarresse says:

      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

      Like

  8. Colin Legg says:

    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.

    Like

  9. 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!

    Like

  10. Lorena says:

    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

    Like

    • Zack Barresse says:

      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

      Like

      • Lorena says:

        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 =)

        Like

      • zbarresse says:

        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

        Like

      • Lorena says:

        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 ?

        Like

  11. Colin Legg says:

    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/

    Like

  12. Nice blog. Tnx

    I use:

    Sub VeryQuickClear(sTableName)
    'clears the table with name sTableName on the current sheet
    
    On Error GoTo VeryQuickClear_err
    
    Application.Calculation = xlCalculationManual
    Range(sTableName).Clear
    Range(sTableName).RemoveDuplicates Columns:=1
    Application.Calculation = xlCalculationAutomatic
    
    VeryQuickClear_exit:
        Exit Sub
        
    VeryQuickClear_err:
        MsgBox Err.Description
        Resume VeryQuickClear_exit
    
    End Sub

    this one is extremely quick

    Like

  13. Francisco Infante says:

    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.

    Like

    • zbarresse says:

      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?

      Like

  14. Morton Wakeland says:

    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!!

    Like

  15. Truptesh says:

    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

    Like

    • zbarresse says:

      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…

      Sub ProtectOnce()
          If ActiveSheet.ProtectContents Then
              On Error Resume Next
              ActiveSheet.Unprotect
              On Error GoTo 0
          End If
          If ActiveSheet.ProtectContents Then
              MsgBox "Couldn't unprotect the sheet", vbExclamation, "Whoops!"
              Exit Sub
          End If
          ActiveSheet.Protect _
                  Contents:=True, _
                  AllowInsertRows:=True, _
                  UserInterfaceOnly:=True, _
                  AllowInsertingHyperlinks:=True, _
                  AllowDeletingColumns:=True, _
                  AllowDeletingRows:=True, _
                  AllowSorting:=True, _
                  AllowFiltering:=True, _
                  AllowUsingPivotTables:=True
      End Sub

      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

      Like

      • Truptesh says:

        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

        Like

      • zbarresse says:

        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…

        ' Manipulate table
        Sub DeleteTableBody()
            Call UnprotectSheet
            With ActiveSheet.ListObjects(1)
                If Not .DataBodyRange Is Nothing Then
                    .DataBodyRange.Delete
                End If
                .ListRows.Add
            End With
            Call ProtectSheet
        End Sub
        
        ' Protection
        Sub UnprotectSheet()
            ActiveSheet.Unprotect
        End Sub
        Sub ProtectSheet()
            If ActiveSheet.ProtectContents Then
                On Error Resume Next
                ActiveSheet.Unprotect
                On Error GoTo 0
            End If
            If ActiveSheet.ProtectContents Then
                MsgBox "Couldn't unprotect the sheet", vbExclamation, "Whoops!"
                Exit Sub
            End If
            ActiveSheet.Protect Contents:=True, _
                                AllowInsertingRows:=True, _
                                UserInterfaceOnly:=True, _
                                AllowInsertingHyperlinks:=True, _
                                AllowDeletingColumns:=True, _
                                AllowDeletingRows:=True, _
                                AllowSorting:=True, _
                                AllowFiltering:=True, _
                                AllowUsingPivotTables:=True
        End Sub

        Like

  16. Truptesh says:

    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

    Like

    • zbarresse says:

      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.

      Like

  17. Pingback: Best of Excel on the Web – Hidden Treasures (2016)

  18. Pingback: Best of Excel on the Web | Candid Sourcing

  19. Pingback: Best of Excel on the Web – Candid Sourcing

  20. Pingback: Hidden Treasures – Best of Excel on the Web (Sep 2012) – Launch Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s