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 then 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. Then all zero value cells in this certain column are 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.
- After selecting the rows, you can proceed to 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 and start a 30-day free trial with no limitations.
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 Sub
3. Press the F5 key to run the code, in the popping up KutoolsforExcel dialog box, select the column range you want to delete entire rows based on the zero values inside, 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.
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!
