How to clear all value cells in an entire 2003 Excel workbook by one go?

How can I clear all value cells in an entire 2003 Excel workbook by one go?

I could do one sheet by going to special functions, however, if a file is composed of many sheets, it would take a lot more effort.

frog

Posted 2012-11-21T05:02:09.617

Reputation: 125

Answers

Based on the comments on a previous answer, I'm assuming you don't want to wipe out formulas? If this is the case, selecting Special (CTRL G > Special > Constants) works, but only on one sheet at a time.

However if you record this process, you could then put it inside a VBA loop.

Sub DeleteConstants()

    Dim wstWorksheet As Worksheet

    For Each wstWorksheet In ActiveWorkbook.Worksheets

        Selection.SpecialCells(xlCellTypeConstants, 23).ClearContents

    Next wstWorksheet

End Sub

Gary

Posted 2012-11-21T05:02:09.617

Reputation: 323

，your input is helpful Gary.Thank you very much!@Jook, you are also being helpful here. – frog – 2012-11-27T03:57:19.360

-1

Select the first worksheet
hold Shift and click on the last worksheet
click on the corner at the top left of the worksheet (left from col A, above row 1).
right click into the worksheet and execute delete contents

That should be it.

Using Shift you can do this even just for specific worksheet. Also you can apply formatting on all selected sheets at once.

Jook

Posted 2012-11-21T05:02:09.617

Reputation: 1 745

this would delete formula in the sheet as well, and also the data area differs from sheet to sheet, to group the sheet and excute one embedded excel button would delete the data only as the same area as the active sheet. – frog – 2012-11-22T04:23:21.207

ok, didn't catch that formula were excluded. the different areas however don't matter, the button in the top left corner of a worksheet selects all cells, using shift, you could even select a specific pattern of cells on every workbook. I'll have a look at this later, but I think you'll end up using VBA for this. – Jook – 2012-11-22T06:17:34.783

Thank you for your effort too, looking forward to sharing more...And VBA of course too – frog – 2012-12-12T04:24:00.943