Many times when we are working on a modelling problem using Microsoft Excel, we might be required to clear the sheet contents. For example, you are just starting out to solve a problem and need to clear the cells without deleting the whole sheet.
Personally, I don’t prefer wasting time on taking my hands to the mouse and then press clear contents.
A better option is to write use some already written VBA macro which can be assigned a shortcut or we can assign this macro to a button on excel sheet. The best part is that using VBA codes, you can achieve a lot such as clearing just the contents, clearing the formatting, clearing everything, clearing the comments etc.
VBA CODE
In this example, I have taken a file named “Pattern_Generation.xlsm”. Yes, all the macro enabled excel workbooks should be saved using the extension of xlsm.
Sub clear_all()
Workbooks("Pattern_Generation.xlsm").Activate
' To clear the contents
ActiveSheet.Cells.ClearContents
' To clear everything including the formatting
ActiveSheet.Cells.Clear
'To clear only formatting
ActiveSheet.Cells.ClearFormats
'To clear the comments
ActiveSheet.Cells.ClearComments
' To clear the outline
ActiveSheet.Cells.ClearOutline
End Sub
It should be noted that the each line of code is preceded with a comment line which says the purpose of each nest line.You can find your purpose and then use that line of code in your VBA Macro to achieve what you want.
Let me know if you feel difficulty in using this code.
