Filter cells that have notes

Copper Contributor

Good morning everyone and thanks for your attention.

 

I have an online Excel with a series of conditional filters, and I wanted a filter for cells that have a note.

In the desktop version, I have seen a simple code in VBA, I have tried it, and it works without problems, but for the web version it is not functional, so I was wondering if there is any way, or any script to perform such an action.

 

Best regards and thanks.

5 Replies
Perhaps if you share that VBA code, someone is capable of writing the same in Office Script?

Good morning and thank you for your reply.

 

I don't know if it was exactly this code, but it would be similar, it works for the desktop version:

 

Function HasComment(r As Range)
'Update 20140718
    Application.Volatile True
    HasComment = Not r.Comment Is Nothing
End Function

 

Thanks

@javifelices The script below filters a table named TB_Test (on Sheet2), for cells in column "Summary" containing a comment:

 

function main(workbook: ExcelScript.Workbook) {
  // Your code here

  let tableCells = workbook.getWorksheet("Sheet2").getTable("TB_Test").getColumnByName("Summary").getRangeBetweenHeaderAndTotal();
  let cellCount = tableCells.getRowCount();
  let allComments = workbook.getComments();
  let commentCell: ExcelScript.Range;
  tableCells.setRowHidden(true);
  for (let i = 0; i < allComments.length; i++) {
    try {
      let commentCell = tableCells.getIntersection(allComments[i].getLocation());
      console.log(commentCell.getAddress());
      commentCell.getEntireRow().setRowHidden(false);
    } catch {}

  }
}

 

Thank you very much for your promptness.

Reviewing the configuration, the administrator does not allow execution of scripts and so on, we will see and study other options.

Best regards and thanks.

@javifelices 

As for me that's bit strange. Preventing Office Script is practically the same as preventing of formulae execution within Excel.