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.
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.|
|Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
Recommended Productivity Tools for Excel/Office
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:
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:
3. In the popping dialog, type the criterion into the textbox next to is greater than. See screenshot:
4. Click OK. Now the only the data greater than 3000 is shown, and the rows whose data is smaller than 3000 are hidden.
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:
4. Click OK, and type the criterion number into the second dialog. See screenshot:
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.
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.|
1. Select the data range and click Kutools > Select > Select Specific Cells. See screenshot:
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:
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:
Now the rows whose data is smaller than 3000 are hidden.
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.
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.
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.
Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free trial of Office Tab!
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 2 years agohi there!
I am running the VBA code but it show an error as under
and Rng.EntireRow.Hidden = Rng.Value < xNumber is highlighted. Can you plz help me in this regard?