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.
How do I reset a table in Excel 2007
-
- 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
Welcome to Eileen's Lounge!
Try this, substituting the correct names for the worksheet and for the table:
Worksheets("MySheet").ListObjects("MyLog").DataBodyRange.Delete
Try this, substituting the correct names for the worksheet and for the table:
Worksheets("MySheet").ListObjects("MyLog").DataBodyRange.Delete
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 2
- Joined: 22 Oct 2010, 14:32
Re: How do I reset a table in Excel 2007
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.
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.
-
- 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
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].
See DataBodyRange Property [Excel 2007 Developer Reference].
Best wishes,
Hans
Hans
-
- 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
Also have a look at my article:
http://www.jkp-ads.com/articles/excel2007tablesVBA.asp
http://www.jkp-ads.com/articles/excel2007tablesVBA.asp