How to hide rows based on cell value in Excel?

Supposing you have a range of data, and you want to hide the rows based on a column value, for instance, if the cell value of the column is smaller than 3000, then hide the row of the cell as below screenshot shown.


In Excel, you can use the Filter function to filter and hide the rows based on cell value.

1. Select the data you want to filter out, and click Data > Filter. See screenshot:

doc hide rows based on value 2

2. Then click on the down arrow to display the filter drop down list, and click Number Filters (or Text Filters) > Greater Than (you can choose other criterion you need from the submenu). See screenshot:

doc hide rows based on value 3

3. In the popping dialog, type the criterion into the textbox next to is greater than. See screenshot:

doc hide rows based on value 4

4. Click OK. Now the only the data greater than 3000 is shown, and the rows whose data is smaller than 3000 are hidden.

doc hide rows based on value 5


Moreover, if you are interested in VBA code, here I can introduce a VBA code to hide rows based on cell value.

1. Press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.

2. Then click Insert > Module to open a new Module window, and paste below VBA code into it.

VBA: Hide rows based on cell value.

Sub HideRow()
'Updateby20150618
Dim Rng As Range
Dim WorkRng As Range
Dim xNumber As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xNumber = Application.InputBox("Number", xTitleId, "", Type:=1)
For Each Rng In WorkRng
    Rng.EntireRow.Hidden = Rng.Value < xNumber
Next
End Sub

3. Then press F5 key to run the VBA, then select the data range you want to hide rows into the popping dialog (excluding headers). See screenshot:

doc hide rows based on value 6

4. Click OK, and type the criterion number into the second dialog. See screenshot:

doc hide rows based on value 7

5. Click OK. Now the rows whose data is smaller than 3000 are hidden.

Tip: If you want to hide rows which are greater than 3000, just change Rng.EntireRow.Hidden = Rng.Value < xNumber to Rng.EntireRow.Hidden = Rng.Value > xNumber, or if you want to hide rows whose data is equal to 3000, change to Rng.EntireRow.Hidden = Rng.Value = xNumber.


Quickly select cells based on criteria in Excel

With Kutools for Excel's Select Specific Cells, you can select cells based on one or two criteria once time. 
doc select specific cells
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

IF you do not like to enable Filter function, neither to VBA, here I introduce you a handy tool – Select Specific Cells of Kutools for Excel to quickly select entire rows based on cell value, then you can hide them.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After free installing Kutools for Excel, please do as below:

Tip. If you want to have a free trial of the Extract Text function, please go to free try Kutools for Excel first, and then go to apply the operation according above steps.

1. Select the data range and click Kutools > Select > Select Specific Cells. See screenshot:

doc hide rows based on value 14

2. In the Select Specific Cells dialog, check Entire row under Selection type section, then select the criterion you need from Specific type list, then type the number or text into the textbox. See screenshot:

doc hide rows based on value 9

3. Click Ok > OK to close dialogs. Now the rows whose data is smaller than 3000 are selected, and you just need to right click at the row header to show the context menu, and click Hide. See screenshot:
doc hide rows based on value 10

Now the rows whose data is smaller than 3000 are hidden.

doc hide rows based on value 11

Tip:

1. If you want to hide rows which including a certain value, you can select Contains and type the certain value into the text box.

doc hide rows based on value 12

2. If you want to hide rows which including a value greater than but less than values, you can select Greater than and Less than, then type the values into the two box, and check And.

doc hide rows based on value 13

With Kutools for Excel’s Select Specific Cells utility, you can identify specific cells’ location, select entire rows or columns based on cell value and so on. Click here to know more about this utility.

Kutools for Excel: 300+ functions you must have in Excel, 30-day free trial from here.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    rohs2011 · 3 months ago
    Hello,
    I have a drop down with multiple reponses. I am trying to hide certain rows dependant on the response, can someone please assist?
    Dropdown options are "CDI", "AR", "Legal", "Multiple" and "Other".
    If response is CDI then hide rows 42-100
    If response is AR then hide rows 19-41 and rows 66-100
    If response is Legal then hide rows 19-66 and rows 88-100
    If response is multiple then don't hide anything
    If response is Other then hide rows 19-88

    Can someone please asssist?
  • To post as a guest, your comment is unpublished.
    denise12 · 8 months ago
    Hi I need some help with my work sheet. I have to build a dynamic questionnaire and I need to have a code that allow me to hide/ Unhide automatically some rows base on a cell information. Example:

    if c6 is "internally" then show me row 7 but hide 8 to 107
    if c6 is "Externally" then hide all the rows from 7 to 107


    if c7 is "yes" then show me row 8 but hide 9 to 107
    if c7 is "No" then show me row 8 but hide 9 to 107
    if c8 is "Critical" then show me row 9 but hide 10 to 107

    if c8 is "Important" show me row 9 but hide 10 to 107
    if c8 is "Ordinary" show me row 9 but hide 10 to 107
    if c8 is "Other" then show me row 9 but hide 10 to 107

    if c9 is "Critical" then show me row 10 but hide 11 to 107
    if c9 is "Important" then show me row 10 but hide 11 to 107
    if c9 is "Ordinary" then show me row 10 but hide 11 to 107
    if c9 is "Other" then show me row 10 but hide 11 to 107

    etc.

    can somebody help me?
  • To post as a guest, your comment is unpublished.
    JAMES · 9 months ago
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row = 1 Then

    If Target.Value = "YES" Then
    Worksheets(2).Activate
    Worksheets(2).Application.Columns("A:Z").Select
    Worksheets(2).Application.Selection.EntireColumn.Hidden = True
    Worksheets(2).Application.Columns("AA:AZ").Select
    Worksheets(2).Application.Selection.EntireColumn.Hidden = False
    ElseIf Target.Value = "NO" Then
    Worksheets(2).Activate
    Worksheets(2).Application.Columns("AA:AZ").Select
    Worksheets(2).Application.Selection.EntireColumn.Hidden = True
    Worksheets(2).Application.Columns("A:Z").Select
    Worksheets(2).Application.Selection.EntireColumn.Hidden = False
    Else
    Worksheets(2).Activate
    Worksheets(2).Application.Columns("A:Z").Select
    Worksheets(2).Application.Selection.EntireColumn.Hidden = False
    Worksheets(2).Application.Columns("AA:AZ").Select
    Worksheets(2).Application.Selection.EntireColumn.Hidden = False
    End If

    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    Jason · 2 years ago
    I got same error
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Sorry I do not know either.
      • To post as a guest, your comment is unpublished.
        Hayden · 2 years ago
        I don't know either Sunny
  • To post as a guest, your comment is unpublished.
    Ali Khan · 4 years ago
    hi there!
    I am running the VBA code but it show an error as under

    Compile error
    Syntex error
    and Rng.EntireRow.Hidden = Rng.Value < xNumber is highlighted. Can you plz help me in this regard?
    TIA