Forum Discussion
javifelices
Sep 24, 2023Copper Contributor
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...
JKPieterse
Sep 25, 2023Silver Contributor
Perhaps if you share that VBA code, someone is capable of writing the same in Office Script?
- javifelicesSep 25, 2023Copper Contributor
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
- JKPieterseSep 25, 2023Silver Contributor
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 {} } }
- javifelicesSep 26, 2023Copper ContributorThank 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.