How to Hide Rows Based on a Cell Value in Excel

This post will guide you how to hide rows based on cell value in Excel. How do I hide rows based on cell value with VBA Macro code in Excel 2013/2016.

Hide Rows Based on Cell Value using Filter Feature


Assuming that you have a list of data in range A1:B6, and you want to hide row if the value of the column B is smaller than or greater than a specific value.  You can use the Filter Function to filter numbers and hide the rows based on cell value. Here are the steps:

#1 select the range of cells that you want to hide rows based on cell values.

hide rows based on cell value1

#2 go to DATA tab, click Filter button under Sort & Filter group. And the filter arrow will be inserted into the first cells in the selected columns.

hide rows based on cell value2

#3 click Filter Arrow icon on the first cell on Sales column, and select Number Filters, then select Greater Than or Less Than menu from the popup submenu list.  And the Custom AutoFilter dialog will open.

hide rows based on cell value3

#4 type one number in the second text box under Sales section. At this example, we will type the number 300, it will filter out all rows that if the cell value is greater than number 300.  Click Ok button.

hide rows based on cell value4

#5 you should see that the rows should be hidden if the cell value is less than 300.

hide rows based on cell value5

Hide Rows Based on Cell Value using VBA Macro


You can also use an Excel VBA Macro to achieve the same result of hiding rows based on cell value. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

hide rows based on cell value6

 

#5 back to the current worksheet, then run the above excel macro. Click Run button.

hide rows based on cell value7

#6 Let’s see the result:

hide rows based on cell value8

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar