0

I have a macro (VBA) that checks a given row/range, and hide the column if the given cell is blank.

Is this possible to do in office.js?

Sub Hide()
  
Dim c As Range

    For Each c In Range("A1:Z1").Cells
        If c.Value = "" Then
            c.EntireColumn.Hidden = True

        End If
    Next c

End Sub

Don´t have many experience in programming. Vba or else. For vba i can record macros and research some similar codes in forums.

But office.js is something i really don´t achieve. Any help will be appreciated .

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45

1 Answers1

1

You can use the rowHidden and columnHidden properties of the Range class in case of Office web add-ins.

The columnHidden property represents if all columns in the current range are hidden. Value is true when all columns in a range are hidden. Value is false when no columns in the range are hidden. Value is null when some columns in a range are hidden and other columns in the same range are not hidden.

The rowHidden property represents if all rows in the current range are hidden. Value is true when all rows in a range are hidden. Value is false when no rows in the range are hidden. Value is null when some rows in a range are hidden and other rows in the same range are not hidden.

Using Office JavaScript API, you can programmatically hide and unhide rows by updating the rowHidden property on the range object. The same can be applied to columns when using the columnHidden property. For example:

Excel.run(function (context) {

    // Hide rows 1-2 in 'Sheet1'
    var range = context.workbook.worksheets.getItem("Sheet1").getRange("1:2");
    range.rowHidden = true;

    return context.sync()
        .then(function() {
            console.log("Rows 1-2 have been hidden.");
        });
}).catch(function (error) {
        console.log(error);
});
Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
  • thanks for your feedback and the time, but my problem is very different than the solution you propose. guess the way it´s to conitnue using vba :) – carlos sousa Jun 09 '23 at 08:01
  • The question was - `Is this possible to do in office.js?`. So, yes - that is possible. You can use the technique described in my post. – Eugene Astafiev Jun 09 '23 at 08:30
  • But do you are sure that you can achieve that with columnHidden property? Do you even try? I am not an expert in programming, as i said, but as all my research and tries i can only achieve that with columnwidth = 0 because of the properties. I really appreciate your time, truly, but the way you answer... it´s not easy to be in a forum, but that works both ways .. sincerely best wishes to you. – carlos sousa Jun 09 '23 at 09:27
  • Let me know if you face with any specific issue. – Eugene Astafiev Jun 09 '23 at 10:27