How to Hide Rows based on Cell Value in Excel2 Easy Methods
In some cases working with an Excel spreadsheet loaded with data can be challenging.
Trying to find the data you need buried within a worksheet can seem nearly impossible.
Fortunately, Microsoft anticipated these challenges when it developed Excel and included tools you can use to locate the information you need by hiding the rows you don’t need to see based on their cell value.
As with most features in Excel, there are at least two ways for you to do this.
The first is by using filters, and the second is by using VBA code.
Here we will show you how to use both of these methods so that you can use the one you prefer.
Using Excel’s Filter Feature To Hide Rows Based on Cell Value
Excel provides a filter feature that can allow users to easily filter and hide rows based on cell values.
To use this feature, select the “Home” tab and look in the “Editing” group for the “Sort and Filter” feature.
Once selected, this will bring up a drop-down list of options for examining your data.
From these options, select “Filter,” and this will bring up the “Filter Icon,” which will appear like an arrow on each of your column headers.
By clicking on one of these arrows, you will bring up a range of options for filtering the cells in each of the corresponding columns.
For example, consider the crowded sheet of data in the worksheet below.
In this sheet, we are looking to filter out each of the rows containing Source Region = West.
In order to do this, all you need to do is select any of the arrows in order to filter the results on the column.
Select any items that you do not want to see to remove the check mark (Such as Grain), and the row will be hidden on your worksheet.
Simply select “OK” when you are done selecting items.
After you have finished selecting what you do and do not want to see, Excel will hide the rows that you did not leave checked in the last step.
If you want to return any of the hidden cells, simply reselect the “Filter” button and select the values you want to see.
Using VBA Codes To Hide Rows Based on Cell Value
With our second method for hiding unwanted rows based on cell value, you will use VBA coding to get the job done.
This is great if you are familiar with using macros and VBA coding in Excel. In this case, macro codes are one of the easiest ways to achieve a conditional result, such as hiding rows based on the contents of their cells.
Using this method, we can alter which rows are visible on our dataset.
For example, we can hide the rows in a column (Category) whenever its value is not equal to a given cell value (Grain).
To get started, navigate to the “Developer” tab and select the “Visual Basic” button in the “Code” group to open the window for Microsoft Visual Basic.
On the left-hand side, ensure that “ThisWorkbook” is selected under the current VBA project possessing the same name as the workbook you are working with.
Next, in the window that appears, look to the “Toolbar” and select “Insert” and “Module.”
Next, paste this code into the “Module.”
Sub Hide_Rows_Based_On_Cell_Value() StartRow = 2 EndRow = 10 ColNum = 3 For i = StartRow To EndRow If Cells(i, ColNum).Value <> “Grain” Then Cells(i, ColNum).EntireRow.Hidden = True Else Cells(i, ColNum).EntireRow.Hidden = False End If Next i End Sub
On your keyboard, press the “F5” key in order to start the macro.
Using this macro code, it will start searching the cells in column 3, beginning with row 2 and ending with row 10.
Whenever it does not find the given value (Grain), it will hide the row.
When you use this code in your own workbook, change these values to the columns, rows, and values based on the appropriate criteria for your purposes.
The biggest advantage to using a macro for hiding rows is that your macros will be stored and accessible by selecting the “Macro” button in the “Codes” group.
This will bring up a window where you can see all of the macros you have created.
You should be able to see the macro that we created earlier.
By selecting this macro and clicking “Run,” you can apply it whenever you need to in the future.
How To Unhide Columns Based on Cell Value in VBA
Once cells are hidden, you may need to restore them again.
Fortunately, it is easy to make these cells visible again; all you need to do is make a slight alteration to the code we just used.
Here is what we will use.
Sub UnhideRows() StartRow = 2 EndRow = 10 ColNum = 3 For i = StartRow To EndRow Cells(i, ColNum).EntireRow.Hidden = False Next i End Sub
In this VBA code, all we have done is set that no matter the value of the cells, each row will be displayed.
You can enter this code and run the macro as we did above.
Conclusion
We have shown you how to Hide Rows Based on Cell Value in your Excel spreadsheet using either the filter features on your ribbon or using VBA codes.
Whether you are a new user that would prefer to use Excel’s built-in features or are highly experienced with VBA codes and would prefer the ease of using macros, these methods will allow you to easily filter and sort your data.