How to use Range.ClearNotes in the xlwings API way

The ClearNotes member of the Range object in Excel is used to remove all cell notes (which are now officially called “comments” in modern Excel versions, but the older “notes” terminology persists in the object model for legacy reasons) from the specified range. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This is particularly useful when you need to clean up annotations, comments, or notes from a dataset before performing analysis or redistribution, ensuring a clean slate.

Syntax and Parameters:
In xlwings, you access the ClearNotes method via the api property of a Range object. The syntax is straightforward as the method does not take any parameters.

range.api.ClearNotes()

• range: This is an xlwings Range object representing the cells from which you want to remove notes.
• .api: This property provides the native Excel VBA object model interface.
• .ClearNotes(): The method call itself. It acts on the range and removes all notes.

Important Note: In Excel’s object model, “notes” refer to the legacy comment format. In Excel for Microsoft 365 and Excel 2021, the traditional “comments” have been replaced by threaded comments, but the ClearNotes method still removes the older style notes. For new threaded comments, you would typically use the ClearComments method. The ClearNotes method will not generate an error if there are no notes in the range; it will simply do nothing.

Code Examples:

1. Clearing notes from a specific range:
   This example removes all notes from cells A1 through C10 on the active sheet.

import xlwings as xw

# Connect to the active Excel instance and workbook
app = xw.apps.active
wb = app.books.active
sheet = wb.sheets.active

# Define the target range
target_range = sheet.range("A1:C10")

# Clear all notes from this range
target_range.api.ClearNotes()

2. Clearing notes from all used cells on a worksheet:
   This example is more comprehensive, clearing notes from every cell that has ever been used on the specified worksheet.

import xlwings as xw

wb = xw.Book("Report.xlsx")
sheet = wb.sheets["DataSheet"]

# Use the 'used_range' property to get all cells with content or formatting
used_range = sheet.used_range
used_range.api.ClearNotes()

# Save the changes
wb.save()

3. Conditional clearing based on cell value:
   This example demonstrates a more advanced scenario where notes are cleared only from cells that meet a specific condition, such as containing a negative number.

import xlwings as xw

app = xw.App(visible=False)
wb = app.books.open("Financials.xlsx")
sheet = wb.sheets[0]

# Loop through a range, e.g., the first column
for cell in sheet.range("A1:A100"):
    # Check if the cell value is a number and less than zero
    if isinstance(cell.value, (int, float)) and cell.value < 0:
        # Clear the note only for this specific cell
        cell.api.ClearNotes()

wb.save()
wb.close()
app.quit()