Forum Discussion

javifelices's avatar
javifelices
Copper Contributor
Sep 24, 2023

Filter cells that have notes

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.

excel
Excel for web
Formulas and Functions
Macros and VBA
Office Scripts
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Sep 25, 2023
    Perhaps if you share that VBA code, someone is capable of writing the same in Office Script?
    • javifelices's avatar
      javifelices
      Copper Contributor
      Sep 25, 2023

      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

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Sep 25, 2023

        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 {}

  }
}

         

Resources