How to hide a row based on cell value with CodeEditor

Question

Hello MS Community!

I am looking for a script in Code Script (not VBA) to hide a row based on a cell value. For example (refer to the below) IF Column A= "No", Hide Row 3. I will have a larger table associated with multiple "No"s, so being able to apply the logic to the larger sheet would be necessary. Thank you!

Answer 1

Hi Nicole

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

Here is a sample code:

Sub Hide_Rows_Based_On_Cell_Value() Dim i As Long Dim LastRow As Long LastRow = ActiveSheet.Cells(Rows.Count, 1). End(xlUp). Row For i = 1 To LastRow If Range("A" & i). Value = "No" Then Rows(i). EntireRow.Hidden = True End If Next i End Sub

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

Hi Anna,

Thank you for your response. Is this in VBA? The version of excel I have uses TypeScript in CodeEditor and does not register any VBA code. Could you please advise me on how to troubleshoot or convert this script? Thanks again for your help!

Best,

Nicole

Answer 3

Please see if this helps:

async function hideRows() { await Excel.run(async (context) => { const sheet = context.workbook.worksheets.getActiveWorksheet(); const range = sheet.getRange("A:A"); range.load("values");

await context.sync();

for (let i = 0; i < range.values.length; i++) { if (range.values[i][0] === "No") { sheet.getRange(A${i+1}).format.rowHidden = true; } }

await context.sync(); }); }

Answer 4

I tried this and here is the output errors I received. I'm struggling a lot with this new code editor so do you have any insight into these errors?

Answer 5

Based on the error messages in your screenshot, it looks like there are a couple of issues with your TypeScript code:

Range is not defined: The Range class is not part of the TypeScript API for Excel. Instead, you should use the Sheet.getRange method to retrieve a range of cells.

sheet.getRange("A:A") does not return a two-dimensional array: The getRange method returns a Range object, which you can load with data using the load method. You can then access the loaded data using the values property, which is a two-dimensional array of values.

Here's an updated version of the code that should work:

async function hideRows() { await Excel.run(async (context) => { const sheet = context.workbook.worksheets.getActiveWorksheet(); const range = sheet.getRange("A:A"); range.load("values");

await context.sync();

for (let i = 0; i < range.values.length; i++) { if (range.values[i][0] === "No") { sheet.getRange(A${i+1}).format.rowHidden = true; } }

await context.sync(); }); }