|
Remove Comments from a Workbook in Excel - All Comments Deleted - Macro VBA
Excel macro that will remove/delete all of the comments from an entire Excel workbook. This means that this macro will remove all of the comments from every worksheet within the current active workbook.
This is a great macro to use in Excel to remove production or creation notes from a workbook or to simply clean up the workbook before it is sent to the end user. This Excel macro will save you a lot of time when you need to delete comments from Excel.
Where to install the macro: Module
Excel Macro to Remove Comments from a Workbook - All Comments Deleted - Macro VBA
Sub Remove_Comments_From_Entire_Workbook()
'This macro loops through the worksheets and delets all of the comments contained in the workbook
Dim wsheet As Worksheet
Dim cment As Comment
For Each wsheet In ActiveWorkbook.Worksheets
For Each cment In wsheet.Comments
cment.Delete
Next
Next
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
I have a workbook that I received from a vendor that when I click in a cell a comment appears. There is no line or comment indicator for each comment. So I went into the tools - options - views - comments section and chose no comments. The comments still appear and when I go to the tool bar - view - comments nothing appears. I checked for any VBA code and didn't see anything. I was able to get around this problem by copy/paste special into a new worksheet, but curiosity is killing this cat. Any ideas?
Hi, I've just tried pasting something into a new Comment and accidentally hit some keys on my laptop and it's messed up my Comments. I have had this once before, but can't remember how I fixed it.
Comments have lost their Borders.
When you EDIT Comment it stays displayed on spreadsheet - i.e. it doesn't just return to the Cell and Indicator.
However, the text in the Comments is still there.
When you hover the mouse over any cells with Comments, nothing is displayed.
I have gone into Tools-Options-General and made sure Indicator+Comments is selected. Objects is Hide All.
Any ideas???
The Silver Fox
Hello,
Is there vba code that can remove comments in a range that a user selects in a workbook? The range may or may not have comments in all cells selected.
Ex: Comments in Cells A1 & B3. User selects range A1:B10 and runs macro to remove comments.
Thanks
The only part I don't know is how to ID the string start with ;
Thanks!
I would like to remove all green little comments on a column of mine saying "The number in this cell is formatted as text or preceded by an apostrophe" without converting it to a number.
Is this possible to do via code? I tried recording a macro when I chose to ignore them but nothing showed up in the VBA editor :-\
Here is a picture example of what I mean, just randomly found on google:
http://www.projectwoman.com/uploaded...ent-763577.jpg
Maybe an insignificant thing I want, but can the line from the comment connecting to the red indicator be removed? Added an example since my comments show up below the selected cell with VBA code.
Hi,
I have a protected sheet in which i have unlocked the majority of the cells as i only need a few to be locked. However, i cant seem to be able to add or remove comments to the unlocked cells and i cant see anythign about comments in the 'allow' list when protecting the sheet.
Anyone got any ideas how i can get roudn this?
Thanks
Hi all,
This is my second recent post. Have been working on an Excel project for too long now. My background is in C, not VB. So bear with me. Now the questions...
I want to write a function to handle all my cell comments needs. The way I am approaching this, I will actually need 2 functions.
The first function needs to create a string from a list. Either from a cell list in Excel or from the Access database it will be in. Either way I want it to look like a list in the cell comment when it is done.
Second function will be multi-purpose. I know how to pass arguments in C. But in VB it's a little fuzzy for me. Well this is what it should do. It will have 3 arguments 1. List or Msg as string, 2. Task as integer, 3. cell location as ?. I will use a select case to perform the selected task.
task will have 3 possible values
0 - delete comment
1 - add a new comment list
2 - hide comment
The code should flow like this
1. delete any existing comments for that cell
2. select case which task was selected (1,2)
3. reset the comment position
My next question is does this seem the right way to go? There will be at least 50-100 cell comments on a worksheet. When the workbook opens all 100 comments will have to be updated. I will have to call this function 100 times.
Thanks for any help in advance
Hmm maybe the title is a little vague .. but it will work;
In the example added a code is running to show the comments (pictures) when the cell is selected. This way you don't have to mouse-over to show the comment, you just use your arrow keys.. Is there a way to not show comments on mouse-over? I only want them to show while the cell is selected, using the VBA code. Because now you can show 2 comments while a cell is selected, and you put your cursor on another cell.
Marco
I have two sheets in a workbook.
Sheet 1 contains a table of shipments broken down by product, shipment date, and number of shipments.
Sheet 2 contains comments/notes on any issues with a particular shipment.
I am trying to add each comment in the issues table to the corresponding cell in the shipments table. Is there a way to do this? Example tables attached. Thanks.
|
|
