Sep 24 2023 12:35 AM
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.
Sep 25 2023 02:58 AM
Sep 25 2023 04:15 AM
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
Sep 25 2023 07:37 AM
@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 {}
}
}
Sep 26 2023 12:11 AM
Sep 26 2023 01:20 AM
As for me that's bit strange. Preventing Office Script is practically the same as preventing of formulae execution within Excel.