CODEWITHSUNDEEP
excelvba
Pravin
Pravin

Reputation: 53

How to remove or delete comments in excel worksheet?

As I click on the particular cells in excel, comments appears that disturb me much so I want vba code to delete all the comments instantly in the active worksheet.

Upvotes: 2

Views: 14998

Answers (2)

DiegoB
DiegoB

Reputation: 1

Please see my reply

Sub delete_comments()
Dim i As Range
     For Each i In ActiveSheet.UsedRange
         i.ClearNotes
     Next i
End Sub

Upvotes: -3

Jazimov
Jazimov

Reputation: 13292

All you really need to do is get a range, then clear comments:

Worksheets("MySheet").Activate 
ActiveSheet.UsedRange.ClearComments

Does that help?

More Detail To get the above code to work, there are several approaches. The one I recommend here is:

  • Open your Excel workbook.
  • Click the Visual Basic option on the Developer tab. This opens a VBA window with a tree control to the left, which shows the worksheets and workbooks.
  • Right-click the worksheet and select Insert Module.
  • In the module window that opens, paste the code I show at the bottom of these instructions.
  • Save the worksheet as type Excel Macro-Enabled Workbook.
  • Close the VBA window.

When back in Excel, hit to bring up the Run Macro window. You should see your RemoveComments macro listed. Now click Run and your comments should be removed.

I actually tested this, so it will work if done properly. If it still doesn't work for you, be sure that the worksheet in question is the first worksheet in your workbook. If it isn't, then change Worksheets(1).Activate in your RemoveComments Sub so that it refers to the correct worksheet.

Sub RemoveComments() 
  Worksheets(1).Activate 
  ActiveSheet.UsedRange.ClearComments 
End Sub

Upvotes: 6

Related Questions