How do I reset a table in Excel 2007

[Yossarian]

Hi,

I have an Excel 2007 spreadsheet containing a table that I use as a log. I would like to reset the table (erase all data and shrink the table) from a macro.

Any suggestions on how to do this?

Thanks.

[HansV]

Welcome to Eileen's Lounge!

Try this, substituting the correct names for the worksheet and for the table:

Worksheets("MySheet").ListObjects("MyLog").DataBodyRange.Delete

[Yossarian]

Hi Hans,

Thanks for the welcome. I'm glad I found it.

Will your suggestion remove the table completely? I'd prefer to leave the headers and formatting and just erase the data, if that were possible.

[HansV]

The line of code that I posted deletes the DataBodyRange of the table; this will delete the data rows but preserve the header row and the formatting.

See DataBodyRange Property [Excel 2007 Developer Reference].

[Jan Karel Pieterse]

Also have a look at my article:
http://www.jkp-ads.com/articles/excel2007tablesVBA.asp