How do I reset a table in Excel 2007

Yossarian
NewLounger
Posts: 2
Joined: 22 Oct 2010, 14:32

How do I reset a table in Excel 2007

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

User avatar
HansV
Administrator
Posts: 79526
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How do I reset a table in Excel 2007

Post by 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
Best wishes,
Hans

Yossarian
NewLounger
Posts: 2
Joined: 22 Oct 2010, 14:32

Re: How do I reset a table in Excel 2007

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

User avatar
HansV
Administrator
Posts: 79526
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How do I reset a table in Excel 2007

Post by 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].
Best wishes,
Hans

User avatar
Jan Karel Pieterse
5StarLounger
Posts: 658
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: How do I reset a table in Excel 2007

Post by Jan Karel Pieterse »

Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com