Table of Contents
Introduction/Problem Statement/Business Need:
This article is to Clear Worksheet or deletes contents, comments, and formats from worksheet using VBA code.
In many a case, a user would like to clear the contents, formats, and comments from a worksheet without manual intervention.
Clear Everything (Contents, Formats, Comments, etc.):
This code will clear the contents, formats, and comments of range (“A1:B10”) of the sheet1.
Sub KDataScience_ClearWorksheet() Sheet1.Range("a1:b10").Clear End Sub
This code will clear the contents of the range (“A1:B10”) of the sheet1. clear contents properties only clear the contents of the specified range, the format remains the same.
Sub KDataScience_ClearContents() Sheet1.Range("A1:B10").ClearContents End Sub
This code will clear the format of a specified range, contents will remain the same.
Sub KDataScience_ClearFormatsOfSheet() Sheet1.Range("A1:B10").ClearFormats End Sub
This code will clear only the comments of the specified range.
Sub KDataScience_ClearCommentsOfSheet() Sheet1.Range("A1:B10").ClearComments End Sub
Clear Entire Worksheet
This code will clear the entire worksheet including formats and comments.
Sub KDataScience_ClearEntireSheet() Sheet1.Cells.Clear End Sub
Delete Worksheet UsedRange
We can also delete the entire worksheet’s used range.
Sub KDataScience_DeleteUsedRange() Sheet1.UsedRange.Clear End Sub
Clear Worksheet Using Variable
We can clear a sheet defined by a variable
Sub KDataScience_ClearSheetUsingObject() 'Decleare a wks variable of type worksheet Dim wks As Worksheet 'Assing value to wks variable Set wks = Sheets("sheet1") wks.Cells.Clear End Sub
If you want to clear worksheet in a workbook then you may use any of the above code instead of manually doing it.