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

We hope it doesn’t look exactly like this, but for now we’ll pretend your spreadsheet has the same kind of nifty formatting shown here:

So can we delete the data yet leave all this…interesting…formatting behind? Of course we can:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.UsedRange.ClearContents

This simple little script starts off by creating an instance of the Excel.Application object and then sets the Visible 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:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)

To delete the values without affecting the formatting takes just one line of code:

objWorksheet.UsedRange.ClearContents

All we’re doing here is taking UsedRange (which represents all the cells that have data in them) and calling the ClearContents method. As you might have guessed, ClearContents will delete the data but leave the formatting alone. When we run the script we’ll end up with a spreadsheet that looks like this:

Admittedly it’s not the most impressive spreadsheet we’ve ever seen, either. But look what happens when you add data to a cell:

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’s a good thing.

OK, so she probably wouldn’t say that if she saw the way we formatted this spreadsheet. But you know what we mean.

By the way, if you’re interested in Microsoft Office Scripting be sure and check out the Office Space column, published every Tuesday and Thursday in the Script Center.