How to Hide Rows with Zero Value in Excel?
How can we hide the rows with zero in Excel? This tutorial guides you to hide rows with zero value in Excel.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Excel does not provide a direct way to hide rows with zero value. For skilled and professional users, the following codes must be a great help in doing it.
Step 1: Press Alt+F11 to open VBA window;
Step 2: Click Module from Insert tab, put the code into Module window;
Step 3: Click Run button or press F5 to apply the VBA.
The VBA code to hide rows with zero value:
Sub HideRowsByZero() 'Update 20131107 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng If Rng.Value = "0" Then Rng.EntireRow.Hidden = True End If Next End Sub
The Select Specific Cells utility of the third party add-in Kutools for Excel can help select the rows containing zero value easily.
Running VBA is a little complex for most of computer users, so an easier way to get rows with zero value hided is necessary. Select Specific Cells in Kutools for Excel will be your good choice to get it done.
After installing Kutools for Excel, apply Select Specific Cells according to these steps:
Step 1: Select the range you want to hide rows with zero value.
Step 2: Click on Kutools > Select Tools > Select Specific Cells. See screenshot:
Step 3: Apply setting in the pop-up dialog box:
1. Choose Entire row in the selection type options;
2. Specify the specific type as Equals 0, and click OK. See screenshot:
Tip: You can add other criteria in the below box to select needed cells.
Step 4: The rows with zero value have been selected, and then you can right-click the row to choose Hide in the menu.
The following example shows how to select rows with zero value by Kutools for Excel. See screenshot:
1. To hide cells with zero value in the entire worksheet, please click to see more…
2. To hide cells with zero value in the selected range, please click to see more…
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.· 5 years agoum excuse me plz halp. I have deleted everything
- To post as a guest, your comment is unpublished.· 6 years agohow can i unhide the rows after i hide it? i used the first method (macros)
- To post as a guest, your comment is unpublished.· 4 years agosame here. I was able to hide the rows using the vb code, how to I reset and display again??
- To post as a guest, your comment is unpublished.· 6 years agoIs there any way using this program that I can hide rows that have zero value? I know that Kutools will allow me to see the rows and then I can right click on them and hide. Is there a quicker way to do this using Kutools? As I have multiple sheets and rows it would be nice if there was a one click way to do this. Any help would be appreciated. Thank you, Lynne
- To post as a guest, your comment is unpublished.· 6 years ago[quote name="Lynne"]Is there any way using this program that I can hide rows that have zero value? I know that Kutools will allow me to see the rows and then I can right click on them and hide. Is there a quicker way to do this using Kutools? As I have multiple sheets and rows it would be nice if there was a one click way to do this. Any help would be appreciated. Thank you, Lynne[/quote]
After selecting rows according to the above section 2, you can right click on the rows and choose to hide them all.