Putting Tables To Work In Excel 2010 – Errata

A week or so ago I included some code in a Blog about using Tables in Excel 2010.  In that blog I included some code that, among other stuff, was supposed to clear all the existing rows of data in the table before importing  new. 

Unfortunately the code I included was wrong in that it would have only deleted 1 row of data beforehand and not all the data in the Table.  You have to iterate through all the rows to delete everything without deleting the Table itself.  If you delete the table and recreate it any formulas referring to the Table name will go to !Refs# and this is not a good thing.

Here is the code with the old code commented out and the new code bold:

 Sub GetWeeklyData()

   Dim Swbk As Workbook
  
   With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
   
    Set Swbk = Application.Workbooks.Open(Range(“EDIDir”).Value & Range(“RequirementsWeeklyFile”).Value, , True)
   
    ThisWorkbook.Activate
  
    With Worksheets(“EDIReleasesWeekly”)

      For i = 1 to .ListObjects(“EDIReleasesWeekly”).ListRows.count  
          .ListObjects(“EDIReleasesWeekly”).ListRows(1).Delete
     Next i

 ‘ .ListObjects(“EDIReleasesWeekly”).ListRows(1).Delete

        Swbk.Worksheets(1).UsedRange.Cells.Copy
        .Cells(1, 1).PasteSpecial Paste:=xlValues
        .Cells(1, 1).PasteSpecial Paste:=xlFormats
        .ListObjects(“EDIReleasesWeekly”).Resize Range(.Cells1,1).CurrentRegion.Address)
    End With
   
    Application.CutCopyMode = False
   
    Swbk.Close False
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
   
End Sub

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

9 Responses to Putting Tables To Work In Excel 2010 – Errata

  1. Ruby says:

    I believe this row .ListObjects(“EDIReleasesWeekly”).ListRows(1).Delete
    should really be .ListObjects(“EDIReleasesWeekly”).ListRows(i).Delete
    with an i instead of a 1.

    • Biggus Dickus says:

      No because everytime you delete a row and increment i eventually you will run out of rows and get an error. See my later post Errata2 which provides a slicker solution.

      Dick

  2. Charlie Hall says:

    Hi Dick,

    I found that deleting a row in a table can be very slow in 2010 and 2007 – especially if the table is large – so recently I changed my code to resize to the new dimensions after a fill.

    Tables will automatically grow to fit more records, but they do not shrink, so after the resize you need to clear the contents of the remaining old data if the table shrunk – but only for presentation purposes the table is the size of the resize regardless of the extra data below

    • Biggus Dickus says:

      Good catch there Charlie. I am not in a position to check right now but wouldn’t the “Resize” do that for me?

      Dick

  3. Charlie Hall says:

    The table is resized, but the old data below the limits of the new table are not deleted or cleared – one has to do that if you need the sheet to look clean

  4. Eric says:

    I appreciated your question, so I thought I would contribute with my work-around:

    Range(“NamedTable”).Clear
    Range(“NamedTable”).RemoveDuplicates Columns:=1

    The first line clears all the rows from the table that is named “NamedTable”, leaving the header row (if it exists).

    The second line takes advantage of the fact that all the cells in the table are now blank, and removes all the duplicate rows (analyzing the “duplicates” in the first column)

  5. Lillianne says:

    Similar to the method posted above, you can also use the databodyrange member of the list object and delete it. It will leave the table intact with column headers and a single blank row (and be resized accordingly). If you then paste your new data into row 2, it will automatically append to the table. Similarly, you can create new data row by row, starting with row 2.

    Code to delete databodyrange:
    activesheet.listojects(“Table1”).databodyrange.delete

    • Biggus Dickus says:

      Good point … Thanx.

      Hope someone out there see some value in this. I think that the fact that no one seems to pay any attention to the Excel Table Object and its capabilities is beyond stupid … it’s incomprehensible IMHO🙂

      Keep up the good work.

      Dick

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