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:
Dim Swbk As Workbook
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
Set Swbk = Application.Workbooks.Open(Range(“EDIDir”).Value & Range(“RequirementsWeeklyFile”).Value, , True)
For i = 1 to .ListObjects(“EDIReleasesWeekly”).ListRows.count
.Cells(1, 1).PasteSpecial Paste:=xlValues
.Cells(1, 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
I believe this row .ListObjects(“EDIReleasesWeekly”).ListRows(1).Delete
should really be .ListObjects(“EDIReleasesWeekly”).ListRows(i).Delete
with an i instead of a 1.
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.
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
Good catch there Charlie. I am not in a position to check right now but wouldn’t the “Resize” do that for me?
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
I appreciated your question, so I thought I would contribute with my work-around:
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)
Thanx … great stuff !!
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:
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.