CODEWITHSUNDEEP
excelms-officeoffice-js
SoftTimur
SoftTimur

Reputation: 5510

Unhide all the cells (including empty columns and rows) of a worksheet

I want to unhide all the cells of a worksheet. I try the following code:

function unhideSheet (sheetName) {
    return Excel.run(function (ctx) {
        var sheet = ctx.workbook.worksheets.getItem(sheetNames[i]);
        sheet.visibility = "Visible";
        sheet.getUsedRange().columnHidden = false;
        sheet.getUsedRange().rowHidden = false;
        return ctx.sync();
    });
}

The problem is getUsedRange does not cover the empty columns or rows that are hidden. As a consequence, the function does not unhide these hidden empty columns or rows.

Does anyone know if it is possible to get the range for the entire worksheet, so that we could unhide everything?

Edit 1: I have calculated that the number of rows in a worksheet is 1048576 (Excel Online). What is odd is that sheet.getRange("1:20").rowHidden = false works well, whereas sheet.getRange("1:1048576").rowHidden = false raises an error This operation is not permitted for the current object. Is it because the range is too big?

I realise that sheet.getRange("1:1048575").rowHidden = false works.

Upvotes: 0

Views: 638

Answers (1)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

To get the entire range of the sheet, use sheet.getRange(), just like this, with no arguments specified. Does that work?

Upvotes: 0

Related Questions