{"id":69583,"date":"2005-06-16T13:05:00","date_gmt":"2005-06-16T13:05:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/06\/16\/how-can-i-delete-data-from-a-spreadsheet-yet-keep-all-the-formatting\/"},"modified":"2005-06-16T13:05:00","modified_gmt":"2005-06-16T13:05:00","slug":"how-can-i-delete-data-from-a-spreadsheet-yet-keep-all-the-formatting","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-delete-data-from-a-spreadsheet-yet-keep-all-the-formatting\/","title":{"rendered":"How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I delete data from a spreadsheet yet keep all the formatting?

— TB<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, TB. From your email it sounds like you have a script that periodically replaces data in a spreadsheet with new, up-to-date values. The spreadsheet has been preformatted, and what you\u2019d like to do is delete all the old values – thus making room for new values – but without deleting any of the formatting. That way you can simply plug in the new values without having to redo the fancy formatting.<\/P>\n

We hope it doesn\u2019t look exactly<\/I> like this, but for now we\u2019ll pretend your spreadsheet has the same kind of nifty formatting shown here:<\/P>\"Microsoft \n


So can we delete the data yet leave all this\u2026interesting\u2026formatting behind? Of course we can:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.UsedRange.ClearContents\n<\/PRE>\n
This simple little script starts off by creating an instance of the Excel.Application<\/B> object and then sets the Visible<\/B> property to True (we set Visible to True just so we can see the changes take place on screen). Next we use these two lines of code to open the file C:\\Scripts\\Test.xls and create an object reference to Sheet1, the first worksheet in the file:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
To delete the values without affecting the formatting takes just one line of code:<\/P>
objWorksheet.UsedRange.ClearContents\n<\/PRE>\n
All we\u2019re doing here is taking UsedRange<\/B> (which represents all the cells that have data in them) and calling the ClearContents<\/B> method. As you might have guessed, ClearContents will delete the data but leave the formatting alone. When we run the script we\u2019ll end up with a spreadsheet that looks like this:<\/P>\"Microsoft \n

Admittedly it\u2019s not the most impressive spreadsheet we\u2019ve ever seen, either. But look what happens when you add data to a cell:<\/P>\"Microsoft \n

As you can see, all the old formats have been retained. All we have to do is add the data and our spreadsheet will be just as pretty as ever. And as Martha Stewart would say, that\u2019s a good thing. <\/P>\n
OK, so she probably wouldn\u2019t say that if she saw the way we formatted this<\/I> spreadsheet. But you know what we mean.<\/P>\n
By the way, if you\u2019re interested in Microsoft Office Scripting be sure and check out the Office Space<\/B><\/A> column, published every Tuesday and Thursday in the Script Center.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I delete data from a spreadsheet yet keep all the formatting?— TB Hey, TB. From your email it sounds like you have a script that periodically replaces data in a spreadsheet with new, up-to-date values. The spreadsheet has been preformatted, and what you\u2019d like to do is delete all the […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,3,5],"class_list":["post-69583","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I delete data from a spreadsheet yet keep all the formatting?— TB Hey, TB. From your email it sounds like you have a script that periodically replaces data in a spreadsheet with new, up-to-date values. The spreadsheet has been preformatted, and what you\u2019d like to do is delete all the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69583","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=69583"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69583\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=69583"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=69583"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=69583"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}