Delete rows that contain zero in a certain column in Excel
When managing data in Excel, there are occasions where you may need to efficiently clean up your dataset by removing rows that contain specific values, such as zeros in a particular column. This ensures your data remains accurate and relevant. This tutorial will guide you through three different methods to delete rows that contain zeros in a specified column: using Excel's Filter function, leveraging Kutools for Excel for a simplified approach, and employing VBA for more advanced users.
Delete rows if cell contains zero with Filter function in Excel
Easily select rows if cell contains zero with Kutools for Excel
Delete rows if cell contains zero with VBA in Excel
Delete rows if cell contains zero with Filter function in Excel
You can use the Filter function to filter out all rows based on the zero values in a certain column, and then delete all visible rows later. Please do as follows.
1. Select the column cells which contain the zero values you want to delete the entire rows based on, then click "Data" > "Filter". See screenshot:
2. Then a drop-down arrow is displaying in the first cell of the selected column, click the arrow, and select "Number Filters" > "Equals" from the drop-down list.
3. In the "Custom AutoFilter" dialog box, enter number "0" into the textbox as below screenshot shown, and then click the "OK" button.
4. All rows containing zero values in the selected column are now filtered out. Please select all visible cells in the filter range and right click on them, select "Delete Rows" from the right-clicking menu. And in the popping up prompt box, click the "OK" button. See screenshot:
5. Now all visible rows are deleted. You can click "Data" > "Filter" again to show all data without zero value cells. See screenshot:
Easily select rows if cell contains zero with Kutools for Excel
Here, we highly recommend using the "Select Specific Cells" utility from "Kutools for Excel". This feature enables you to effortlessly select entire rows within a specified range that contain zeros in a particular column. After selecting these rows, you can manage them manually.
After downloading and installing Kutools for Excel, click "Kutools" > "Select" > "Select Specific Cells" to open the "Select Specific Cells" dialog box. Then configure the dialog as follows.
- Select the column range where you want to remove entire rows based on the zero values.
- Select the "Entire row" option in the "Selection type" section.
- Select "Equals" in the first "Specific type" drop-down list, then enter the number "0" into the text box.
- Click the "OK" button.
Result
All rows containing zero values in the specified column will be selected automatically.
- Once the rows are selected, you can delete them manually as needed.
- To use this feature, you should have Kutools for Excel installed on your computer. Go to download Kutools for Excel now.
Delete row if cell contains zero with VBA in Excel
This section will show you the VBA method to delete all rows if zero values exist in a certain column in Excel.
1. Press "Alt" + "F11" keys simultaneously to display the "Microsoft Visual Basic for Applications" window.
2. In the "Microsoft Visual Basic for Applications" window, click "Insert" > "Module", then copy and paste the following VBA code into the module window.
VBA: Delete entire rows if zero exists in a certain column
Sub DeleteZeroRow()
'Updateby20140616
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)
Application.ScreenUpdating = False
Do
Set Rng = WorkRng.Find("0", LookIn:=xlValues)
If Not Rng Is Nothing Then
Rng.EntireRow.Delete
End If
Loop While Not Rng Is Nothing
Application.ScreenUpdating = True
End Sub3. Press the "F5" key to run the code, in the popping up "KutoolsforExcel" dialog box, select the column range containing zeros to delete the entire rows, then click the "OK" button. See screenshot:
Then all rows based on the zero values in specified column range are deleted immediately.
By mastering these methods, you can streamline your data cleaning process in Excel, ensuring that your datasets are free from unwanted rows containing zeros. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.
Demo: Delete row if cell contains zero with Kutools for Excel
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in