Clear contents and formatting of an Excel cell with a single command

vba excel excel-2013
MackM picture MackM · Aug 7, 2015 · Viewed 185.7k times · Source

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?

Answer

MackM picture MackM · Aug 7, 2015

Use the .Clear method.

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

MSDN documentation here.

Related questions

Remove Duplicates from range of cells in excel vba

I'm trying to remove duplicates in excel 2013 VBA. but I'm getting error "object does not support this property or method". The problem is I don't have static range to select. I want remove duplicates from the column heaader 'abcd'. Cells.…

Read More
No Add-In Tab Though Add-In is Loaded and Working

I wrote an Excel VBA add-in over ten years ago, which uses the old Commandbars to put a menu in Excel's Tools menu (in Excel 2003 and earlier) or in Excel's Add-Ins tab (in Excel 2007 and later). I've never had a …

Read More
VBA for Excel throws "Object variable or with block variable not set" when there is no Object

In my code, I have declared these variables: Dim Field_Name, Datatype, row As Integer Then, inside a For loop, I have this code: Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column Datatype = Worksheets(i).UsedRange.Find("Datatype").Column …

Read More