30

If you want to clear the contents of a cell or range in Microsoft Excel, you can use .ClearContents. If you also want to clear the formatting, you can use .ClearFormats.

Sheets("Test").Range("A1:C3").ClearContents
Sheets("Test").Range("A1:C3").ClearFormats

If you want to do both, you can use .Delete, but then the other cells in the spreadsheet shift to replace the deleted cells.

Sheets("Test").Range("A1:C3").Delete

How can you remove the contents and the formatting of a cell or range in VBA with a single command, without affecting the rest of the worksheet?

Community
  • 1
  • 1
MackM
  • 2,906
  • 5
  • 31
  • 45

1 Answers1

55

Use the .Clear method.

Sheets("Test").Range("A1:C3").Clear

MSDN documentation here.

MackM
  • 2,906
  • 5
  • 31
  • 45
  • 4
    I spent an embarrassingly long time looking for that solution, hopefully it will save someone else the trouble. – MackM Aug 07 '15 at 18:57
  • 2
    **Word of Caution**: `.Clear` API also removes the formula if any in the target cells. In that regard `ClearContents` is much safer. – RBT May 14 '18 at 12:40
  • 2
    @RBT I'm don't think that's the case, the [documentation for `.ClearContents`](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-clearcontents-method-excel) specifically mentions removing formulas. The only further consideration I can think of before using `.Clear` is that it will also clear any notes you have left on that cell. – MackM Jul 02 '18 at 19:39
  • @MackM Please check the documentation that you've referred in your own post for `.Clear` method. Example section of the documentation page says that - `This example clears the formulas and formatting in cells A1:G37 on Sheet1.` – RBT Jul 02 '18 at 23:56
  • the documentation has been updated: `Range.Clear [...] clears formulas and values [...] but leaves the cell formatting and conditional formatting intact` – martin.lindenlauf Aug 21 '19 at 21:18
  • @martin.lindenlauf Where do you see that? I see it in the documentation for [`.ClearContents`](https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.ClearContents) but not for [`.Clear`](https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.Clear) – MackM Aug 22 '19 at 14:55
  • @MackM you are right. I can't verify my statement - seems I went wrong. Sorry! – martin.lindenlauf Sep 24 '19 at 12:00