How to: Clear Cells of Content, Formatting, Hyperlinks and Comments

• Sep 19, 2023
• 2 minutes to read

This example demonstrates how to clear worksheet cells.

Clear All

To remove all cell information (content, formatting, hyperlinks and comments), call the Worksheet.Clear method.

Clear Cell Content

To remove cell content only (value and formula), call the Worksheet.ClearContents method, or assign the CellRange.Value property to null or to CellValue.Empty.

Clear Cell Formatting

To remove cell formatting only, call the Worksheet.ClearFormats method, or apply the Normal style to cells via the CellRange.Style property.

Clear Cell Hyperlinks

To remove any hyperlinks contained in a cell or cell range, call the Worksheet.ClearHyperlinks method.

You can also get hyperlinks contained in the specified cell range via the HyperlinkCollection.GetHyperlinks method and remove them using the HyperlinkCollection.Remove method of the Worksheet.Hyperlinks collection. In this case, cell formatting is also cleared.

Clear Cell Comments and Simple Notes

To remove any comments and simple notes contained in a cell or cell range, call the Worksheet.ClearComments method.

You can also call the ThreadedCommentCollection.Remove or ThreadedCommentCollection.RemoveAt method to remove threaded comments. The CommentCollection.Remove or CommentCollection.RemoveAt method allows you to remove simple notes.

View Example

CellActions.cs

// Remove all cell information (content, formatting, hyperlinks and comments).
worksheet.Clear(worksheet["C2:D2"]);

// Remove cell content.
worksheet.ClearContents(worksheet["C3"]);
worksheet["D3"].Value = null;

// Remove cell formatting.
worksheet.ClearFormats(worksheet["C4"]);
worksheet["D4"].Style = workbook.Styles.DefaultStyle;

// Remove hyperlinks from cells.
worksheet.ClearHyperlinks(worksheet["C5"]);

Hyperlink hyperlinkD5 = worksheet.Hyperlinks.GetHyperlinks(worksheet["D5"])[0];
worksheet.Hyperlinks.Remove(hyperlinkD5);

// Remove comments from cells.
worksheet.ClearComments(worksheet["C6"]);

Comment commentD6 = worksheet.Comments.GetComments(worksheet["D6"])[0];
worksheet.Comments.Remove(commentD6);

CellActions.vb

' Remove all cell information (content, formatting, hyperlinks and comments).
worksheet.Clear(worksheet("C2:D2"))

' Remove cell content.
worksheet.ClearContents(worksheet("C3"))
worksheet("D3").Value = Nothing

' Remove cell formatting.
worksheet.ClearFormats(worksheet("C4"))
worksheet("D4").Style = workbook.Styles.DefaultStyle

' Remove hyperlinks from cells.
worksheet.ClearHyperlinks(worksheet("C5"))

Dim hyperlinkD5 As Hyperlink = worksheet.Hyperlinks.GetHyperlinks(worksheet("D5"))(0)
worksheet.Hyperlinks.Remove(hyperlinkD5)

' Remove comments from cells.
worksheet.ClearComments(worksheet("C6"))

Dim commentD6 As Comment = worksheet.Comments.GetComments(worksheet("D6"))(0)
worksheet.Comments.Remove(commentD6)

The image below shows how cells can be cleared (the workbook is opened in Microsoft® Excel®).

To delete an entire cell or range of cells from the worksheet, use the Worksheet.DeleteCells method. Refer to the following example for more information: How to: Delete a Cell or Range of Cells.