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

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

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’s because it didn’t 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.

Talk about anti-climactic.

Here’s what the script looks like:

Const xlHAlignCenter = -4108

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
For i = 1 to 14
    objExcel.Cells(i, 1).Value = i
    objExcel.Cells(i, 2).Interior.ColorIndex = i
Next
Set objRange = objWorksheet.UsedRange
objRange.HorizontalAlignment = xlHAlignCenter 
objRange.Font.Bold = True
Wscript.Sleep 5000
objRange.ClearFormats

We should note that we’re going to ignore most of the lines of code in this script; that’s because we’ve included those lines simply to give us a formatted Excel spreadsheet to work with. If you’d like to know more about scripting with Microsoft Excel you might want to take a peek at the Office Space archive; there you’ll find links to all sorts of articles that show you how to script with Excel.

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:

Set objRange = objWorksheet.UsedRange

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’s what the UsedRange does: it gives us a Range 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 “used” cells.

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:

objRange.HorizontalAlignment = xlHAlignCenter 
objRange.Font.Bold = True

That just adds a little more formatting to the mix. Next we pause the script for 5 seconds (5,000 milliseconds). We don’t have 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.

And so how do we remove all that formatting? Here’s how:

objRange.ClearFormats

See: we weren’t 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 method and all formatting will be removed. Hey, we told 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.