Forum Discussion

Jshannon1755's avatar
Jshannon1755
Copper Contributor
Aug 07, 2023

Auto HIDE ROWS based on 2 cells

Hi Community;

I have an Excel Sheet for inventory with a column indicating status: 'A' = active, 'IN' = inactive units.

I would like to hide/ unhide individual rows based on their status and a drop down list in cell B2 'SHOW' or 'HIDE'.

 

I have seen many post about selecting specific cells, but not a dynamic range based on the show hide value and a column value in the row as in my description above.

How to accomplish this, especially when rows are added for new inventory?

Cell B2 drop down list SHOW or HIDE inactive units.
Column B is the status A or IN
Rows 4 - infinity are the individual inventory items
There are rows at the top (Row 1,-2) and bottom (Row 44-48) of the sheet which should not be included in the HIDE / UNHIDE Range, containing text labels and instructions.

Currently, Rows 4-42 are populated with inventory.
I can place a blank copy/paste row at the bottom (currently row 43) if custom formulas or formatting need to be added to cells in a row in order to accommodate adding a new row to the inventory range.

 

Any assistance is appreciated. 

 

 

excel
Formulas and Functions
Macros and VBA
office 365
  • mtarler's avatar
    mtarler
    Silver Contributor
    Aug 07, 2023

    To do this you need either VBA (macro) or use scripting. Since VBA isn't supported in online Excel but scripting is supported in both I recommend scripting. Here is a script I created to filter/unfilter (i.e. I have a single button to toggle between filtered or not filtered)

     

    /**
* This script filters table.
*/
function main(workbook: ExcelScript.Workbook) {
//Unlock worksheet
let wksht = workbook.getActiveWorksheet()
wksht.getProtection().unprotect()
//Get table
let tbl = workbook.getTable("Table1")
// Sort the table based on the column.
if ( tbl.getAutoFilter().getIsDataFiltered() ) {
tbl.clearFilters()
} else {
tbl.getColumn(6).getFilter().applyValuesFilter([""]);
}
wksht.getProtection().protect()
}

     



    This will unprotect the sheet (no password), either clear any filters or apply filter to show only "" on column 6, and then re-protect the sheet (no password).
    You do need a business or school account to use scripting and it will need a button press (but that is no different than your drop down option and doesn't require a macro always checking on any change on the whole sheet).  I would imagine you might want the button named something like "Toggle Show Active/All" and change the code to column 2 and "A" instead of "".  And don't forget to change the table name from "Table1" if needed.

Resources