Hide rows / columns based on condition

[cseewald1973]

Please provide an office script to hide/show columns based on a value in the row above the column heades. Also, hiw to hide/show rows based in the 1st value in the row (Column A).

The code should run until the 1st blank column / row is found

Thanks

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 98c58cbf-aaf7-3d3a-48f4-ce9cebcf331c
• Version Independent ID: 74821d34-eab6-ec0f-7a9f-55aca81975d9
• Content: Basic scripts for Office Scripts in Excel - Office Scripts
• Content Source: docs/resources/samples/excel-samples.md
• Product: excel
• Technology: scripts
• GitHub Login: @o365devx
• Microsoft Alias: o365devx

[AlexJerabek]

Hi @cseewald1973,

Thanks for the sample request. I'm not sure I completely understand your scenario. Is it something like the following table?

In that worksheet, we'd want the script to make sure columns A and C are shown, while B is hidden. Is this an accurate version of what you're asking for?

[cseewald1973]

Hi Alex Thanks for the quick response. You have understood correctly, below is the VBA code I used to achieve this using a value of 0 to decide if a column must be hidden or not . I have also added a few other useful lines of code. Dim cell As Range Dim ColWidthRange As Range Dim ColSpan As Range Dim c As Range 'User can specify the column width of non hidden columns by providing a value Set ColWidthRange = Worksheets("Data").Range("F2") Application.Cursor = xlWait ActiveSheet.Unprotect ***@***.***" Application.StatusBar = "Hiding columns..." 'Prevent the screen from refreshing and doing calculations Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ActiveSheet.DisplayPageBreaks = False 'All 0 loaded assessments will be hidden For Each c In Range("B2:AY2").Cells If c.Value = 0 Then c.EntireColumn.Hidden = True Else c.EntireColumn.ColumnWidth = ColWidthRange End If Next c 'set the sheet back to the way it was before the hide/show operation ActiveWindow.ScrollColumn = 1 Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic ActiveSheet.DisplayPageBreaks = True ActiveSheet.Protect ***@***.***", True, True Set cell = Nothing Set ColWidthRange = Nothing Set ColSpan = Nothing Application.Cursor = xlDefault Here is how I hide unused rows where a student name has not been provided and the cell value is still "AwaitingStudent", these rows must be hidden until a student name is specified ActiveSheet.Unprotect ***@***.***" Application.Cursor = xlWait Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.StatusBar = "Hiding unused rows........." Dim LastRow As Long, c As Range Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row LastRow = LastRow + 1 On Error Resume Next For Each c In Range("A4:A" & LastRow) If c.Cells.Text = "AwaitingStudent" Then c.EntireRow.Hidden = True Else c.EntireRow.Hidden = False End If Next On Error GoTo 0 Application.EnableEvents = True ActiveSheet.DisplayPageBreaks = True Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.StatusBar = "" Application.Cursor = xlDefault Here's how I hide/unhide all columns that I dont need to see to prevent clutter Sub HideAllUnusedColumns() ActiveSheet.Unprotect ***@***.***" Application.ScreenUpdating = False For Each cell In Intersect(ActiveSheet.UsedRange, Range("B1:AY1")) cell.EntireColumn.Hidden = cell.Value = "" 'And Not IsEmpty(Cell) Next cell ActiveSheet.Protect ***@***.***", True, True End Sub Here's how to create a header in Excel Sub HeaderFromC12() On Error Resume Next Application.StatusBar = "Creating header..." ActiveSheet.PageSetup.LeftHeader = Range("Cover!C12").Value ActiveSheet.PageSetup.LeftHeader.Font.Size = 20 End Sub In this sample I copy data from one column to another depending on which semester we are in, then I reset the worksheet for the following semester For Each cell In Range("Cover!D1:Cover!D1") ' Where to look for the date If cell.Value = "Semester 1" Then ' The date Range("BA5:BA31").Select ' What is the range to copy Selection.Copy ' Copy it Sheets("Perc").Select ' Select the sheet Range("BB5:BB31").Select ' Where to paste the data in destination sheet Range("BB5:BB31").PasteSpecial Paste:=xlPasteValues Sheets("Perc").Select ' Go back to the templete sheet Range("BB5").Select Application.CutCopyMode = False End If Next

…

On Fri, 12 Aug 2022 at 19:56, Alex Jerabek ***@***.***> wrote: Hi @cseewald1973 <https://github.com/cseewald1973>, Thanks for the sample request. I'm not sure I completely understand your scenario. Is it something like the following table? [image: image] <https://user-images.githubusercontent.com/38896772/184415899-1f82f275-cc2c-40d1-9d2a-1e21ea030282.png> In that worksheet, we'd want the script to make sure columns A and C are shown, while B is hidden. Is this an accurate version of what you're asking for? — Reply to this email directly, view it on GitHub <#529 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKSFEL3VFTW2F6AU5Q4HEU3VY2F3ZANCNFSM56L7B2SA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Regards Christopher Seewald

[AlexJerabek]

Hi @cseewald1973,

Thanks for explaining the scenario a bit more. Here's a script that should handle your original scenario:

function main(workbook: ExcelScript.Workbook) {
  const dataWorksheet = workbook.getWorksheet("Data")
  const usedRange = dataWorksheet.getUsedRange();

  const topRowValues = usedRange.getRow(0).getValues();
  const columnCount = usedRange.getColumnCount();
  for (let col = 0; col < columnCount; col++) {
    if (topRowValues[0][col] == 0) {
      usedRange.getColumn(col).setColumnHidden(true);
    } else {
      usedRange.getColumn(col).setColumnHidden(false);
    }
  }
}

Here's the test worksheet before running the script:

And here's the test worksheet after running the script:

I can look into adding a version of this as a sample. Please let me know if this is accurately capturing your original ask.

As for the larger scenario you've shared, I'm happy to help with targeted questions, though I can't translate macros to scripts. Stack Overflow is a great place to ask larger coding questions (make sure to tag your questions with "office-scripts".

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

This issue has been closed due to inactivity. Please comment if you still need assistance and we'll re-open the issue.