{"id":68043,"date":"2006-02-02T11:45:00","date_gmt":"2006-02-02T11:45:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/02\/02\/how-can-i-remove-all-the-formatting-from-an-excel-spreadsheet\/"},"modified":"2006-02-02T11:45:00","modified_gmt":"2006-02-02T11:45:00","slug":"how-can-i-remove-all-the-formatting-from-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-remove-all-the-formatting-from-an-excel-spreadsheet\/","title":{"rendered":"How Can I Remove All the Formatting from an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I remove all the formatting from an Excel spreadsheet?

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

Hey, MT. You know, several years ago one of the Scripting Guys and his family went to the Blizzard Beach water park in Orlando. While there this Scripting Guy went down the Summit Plummet, reputed at the time to be the highest and fastest water slide in the world. What this Scripting Guy (who was dared by his son to go down the slide, leaving him little choice in the matter) remembers most about the experience is that he stood in line for about 45 minutes and then listened to a minute or so of safety instructions, all for the chance to experience a ride that maybe lasted 3 or 4 seconds, max. <\/P>\n

On the bright side, though, no Scripting Son could call him chicken. Or at least not when it came to water slides.<\/P>\n

We were reminded of this kind of experience – a huge buildup for something that was over before you knew it – as we sat down to write a script that addressed your question. That\u2019s because it didn\u2019t make much sense to show you how to clear all the formatting from a spreadsheet unless we had a spreadsheet that included several different types of formatting. Therefore we wrote a bunch of code to create a formatted spreadsheet. And then, when the time came, we found out that we needed just one line of code to clear all that formatting.<\/P>\n

Talk about anti-climactic.<\/P>\n

Here\u2019s what the script looks like:<\/P>

Const xlHAlignCenter = -4108<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
For i = 1 to 14\n    objExcel.Cells(i, 1).Value = i\n    objExcel.Cells(i, 2).Interior.ColorIndex = i\nNext<\/p>\n
Set objRange = objWorksheet.UsedRange\nobjRange.HorizontalAlignment = xlHAlignCenter \nobjRange.Font.Bold = True<\/p>\n
Wscript.Sleep 5000<\/p>\n
objRange.ClearFormats\n<\/PRE>\n
We should note that we\u2019re going to ignore most of the lines of code in this script; that\u2019s because we\u2019ve included those lines simply to give us a formatted Excel spreadsheet to work with. If you\u2019d like to know more about scripting with Microsoft Excel you might want to take a peek at the Office Space archive<\/B><\/A>; there you\u2019ll find links to all sorts of articles that show you how to script with Excel.<\/P>\n
However, we will at least give you a brief summary of what the script does. To begin with, we create an instance of Excel and then create a new workbook and a new worksheet. We add numbers and change the background color of a bunch of cells on that worksheet, then run smack-dab into this line of code:<\/P>
Set objRange = objWorksheet.UsedRange\n<\/PRE>\n
Our goal today is to write a script that removes all the formatting from the entire spreadsheet; as you might expect, the easiest way to do that is to first select the entire spreadsheet. That\u2019s what the UsedRange<\/B> does: it gives us a Range<\/B> object that encompasses the, well, used portion of the spreadsheet. For example, suppose we have data in cell A1 and data in cell G37; the used range consists of cells A1 and G37, as well as all the cells in between. Obviously a quick and easy way to grab all the \u201cused\u201d cells.<\/P>\n
Once we have this range we take advantage of it right away, using these two lines of code to boldface and center all the text on the spreadsheet:<\/P>
objRange.HorizontalAlignment = xlHAlignCenter \nobjRange.Font.Bold = True\n<\/PRE>\n
That just adds a little more formatting to the mix. Next we pause the script for 5 seconds (5,000 milliseconds). We don\u2019t have<\/I> to do that, but it gives you a chance to gaze at all the pretty formatting, right before the script comes in and removes it all.<\/P>\n
And so how do<\/I> we remove all that formatting? Here\u2019s how:<\/P>
objRange.ClearFormats\n<\/PRE>\n
See: we weren\u2019t kidding: one line of code. Using our Range object (the one that encompasses the used range) all we have to do is call the ClearFormats<\/B> method and all formatting will be removed. Hey, we told<\/I> you it was a bit anti-climactic. On the bright side, though, if you run this script no one will be able to call you chicken; at least not when it comes to removing all the formatting from an Excel spreadsheet.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I remove all the formatting from an Excel spreadsheet?— MT Hey, MT. You know, several years ago one of the Scripting Guys and his family went to the Blizzard Beach water park in Orlando. While there this Scripting Guy went down the Summit Plummet, reputed at the time to be […]<\/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-68043","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 remove all the formatting from an Excel spreadsheet?— MT Hey, MT. You know, several years ago one of the Scripting Guys and his family went to the Blizzard Beach water park in Orlando. While there this Scripting Guy went down the Summit Plummet, reputed at the time to be […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68043","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=68043"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68043\/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=68043"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=68043"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=68043"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}