How to Delete Row in Excel if Cell is Zero [5 Ways]

Read this Excel tutorial to learn various ways to delete one or many rows in Excel if any cell in these rows contains zeros.

Sometimes, you may need to delete rows from a data table on your Excel worksheet that contain zero values in some cells. If it’s a small, say 10 by 10 table, you could pinpoint the cells containing zeros and delete the rows.

However, what if there are tens of thousands of rows in the table or worksheet? Do you plan to find and delete rows containing zeros manually? It could take days if not months.

When you face such a situation, you can tackle it smartly and earn respect from clients, employers, and colleagues. All you need to do is read this article until the end and practice the methods while you’re reading. Let’s get started!

Reasons to Delete Row in Excel if Cell Contains 0

Find below why you may need to delete rows that contain zeros in any cell:

• You want to eliminate applicants or candidates who score zero on certain tests.
• If zeros in cells are of no use, you can delete them automatically to unclutter the table or database.
• In certain data visualization zeros are considered irrelevant. Delete these rows to keep the data visualization relevant.
• When trying to reduce the size of your spreadsheet, consider deleting rows containing zeros in cells that are of primary interest.
• If your data table or worksheet is subject to further mathematical or statistical analysis, zeros in cells could create errors in calculations.
• Zeros on a report could mislead the audience. So remove them using the manual and automated methods mentioned here to make your reports readable.

Delete Row In Excel If Cell Contains Zero Using Filter

You can use the filter tool of Excel to delete cells containing zeros in Excel. Since cells are within rows, Excel will also delete the respective rows. Find below the simple steps to use the filter in Excel:

1. Highlight all the column headers in the table (columns A to E in the example).
2. Press Ctrl + Shift + L on your keyboard.
3. Go to the column for which you need to filter out zeros (column C).
4. Click the filter drop-down arrow.
5. Uncheck the Select All checkbox.
6. Checkmark the zero (0) box.
7. Click OK.

8. Select the first row by clicking its number on the left-side panel.
9. Press the Ctrl key.
10. Now select the remaining rows that contain zeros in the filtered cells.

11. Right-click on the row numbers panel and click Delete Row.

12. Press Ctrl + Shift + L again to disable filtering.

You should now see a clean data table without minus rows that contained zeros in any of the cells.

Use the Sort Tool to Delete Rows Containing Zeros in Excel

Another way to eliminate the rows containing zeros in any of their cells in Excel is by sorting the data table using the Sort & Filter tool first. Then, delete the rows of interest. Here’s how:

1. Select the cell range where you want to perform the sort.
2. Alternatively, press Ctrl + A to select the whole worksheet.
3. Press Alt > H > S > U keys one after the other to open the Sort dialog box.
4. Click the Sort by drop-down to select the column by which you want to sort the data table. In this example, it’s column C (Sort).
5. Click the drop-down arrow of the Order field and choose the Largest to Smallest option.
6. Click OK to apply the sorting rule.

7. You should now see all the rows containing zero in the selected cell in the sorting rule are arranged together.

8. Select all these rows.
9. Right-click and choose Delete.

That’s it! Your data table or worksheet is now free of rows with cells containing the number 0.

Utilize the Find and Replace Tool

You can put the Find and Replace tool to good use by locating cells containing zero values in your Excel worksheet or database. Once done finding all such cells, you must manually select the rows housing these cells and delete them. Find below the complete step-by-step instructions:

1. Press Ctrl + F on your worksheet.
2. On the Find and Replace dialog box, make the following selections:
   • Find what: 0
   • Within: Sheet
   • Search: By Rows
   • Look in: Values
   • Checkmark Match entire cell contents
3. Once done, click Find All.
4. The Find and Replace tool will extend below to show all the occurrences of the number 0.

5. Press Ctrl + A to select all those cells with zero values inside Find and Replace.
6. Now, Excel will highlight only the cells containing the number 0.
7. Click the Close button on the Find and Replace dialog box.

5. Now, right-click and choose Delete on the context menu that appears.
6. On the Delete dialog box, select the Entire Row option, and click OK.

That’s all! Excel will delete all such cells and rows that have the number 0.

Use Excel VBA to Delete Row In Excel If Cell Contains 0

So far, you learned the manual methods to delete rows in Excel if cells contain 0. If the numbers of rows are exhaustive, you’ll need to automate the process. With the following VBA scripts, you can delete these rows in a second:

VBA Script to Delete All Rows Containing Zeros

1. Press Ctrl + F11 to bring up the Excel VBA Editor tool.
2. There, click the Insert button and choose Module.
3. Into the blank module, copy and paste this VBA script:

Sub DeleteRowsWithZero()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim lastRow As Long
    
    ' Specify the worksheet name where you want to delete rows
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Set the range to search for cells with value 0
    Set rng = ws.Range("A1:C8")
    
    ' Loop through each cell in the range
    For Each cell In rng
        ' Check if the cell value is 0
        If cell.Value = 0 Then
            ' Delete the entire row
            cell.EntireRow.Delete
        End If
    Next cell
End Sub

4. Click the Save button and press Yes in the warning message that appears.
5. Close the Excel VBA Editor.

6. Now, hit Alt + F8 to launch the Macro dialog box.
7. There select the DeleteRowsWithZero macro and click Run.

Excel VBA will automatically find and delete cells containing 0s. With these cells, Excel will also delete the corresponding rows.

You can make the above VBA code also work for your worksheet or data table by modifying the following code elements to suit your data:

• "Sheet1": This should be the worksheet name of your own worksheet.
• ws.Range("A1:C8"): Replace A1:C8 with the actual cell range on your worksheet.

VBA Script to Delete Rows Containing Zeros in Multiple Worksheets

Suppose, there are multiple worksheets in your workbook. You must delete rows containing cells with zero values. In this scenario, you can use the following Excel VBA code:

Sub DeleteRowsWithZero()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim lastRow As Long
    
    ' Specify the worksheet names where you want to delete rows
    Dim sheetNames As Variant
    sheetNames = Array("Sheet1", "Sheet2", "Sheet3") ' Add more sheet names as needed
    
    ' Loop through each worksheet
    For Each sheetName In sheetNames
        Set ws = ThisWorkbook.Worksheets(sheetName)
        
        ' Set the range to search for cells with value 0
        Set rng = ws.Range("A1:C8")
        
        ' Loop through each cell in the range
        For Each cell In rng
            ' Check if the cell value is 0
            If cell.Value = 0 Then
                ' Delete the entire row
                cell.EntireRow.Delete
            End If
        Next cell
    Next sheetName
End Sub

Follow the steps mentioned previously to create a VBA macro using this VBA script. Then, run the macro to delete cells containing zeros along with the corresponding rows.

In the above code, replace worksheet names like "Sheet1", "Sheet2", and "Sheet3" with the exact names of worksheets in your workbook.

If you want to apply the VBA code in more than three worksheets, add the sheet name preceded by a comma and within double quotes.

Delete Rows Containing Zeros in Excel Using Office Scripts

Do you automate data analysis projects on the Excel desktop and online apps using Office Scripts? Then you can use this method to automate the part where you must delete rows in your worksheet containing cells with zero values.

You can use this method only if you’ve got Microsoft 365 Business Standard or a better subscription.

Also, you need to install the Excel for Microsoft 365 desktop app to use Office Scripts. Or, you can go to Excel for the web app. In both cases, you’ll need active internet to use Office Scripts.

Find below the Office Scripts code and the steps to use it:

1. Click the Automate tab on the Excel ribbon menu.
2. Click the New Script button inside the Scripting Tools commands section.
3. Inside the Code Editor panel, copy and paste the following script:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Sort the range range C2:C8 on selectedSheet
	selectedSheet.getRange("C2:C8").getSort().apply([{ key: 0, ascending: false }], false, true, ExcelScript.SortOrientation.rows);
}

4. Click Save script to save the code for future use.
5. Hit the Run button to execute the code.

In the above script, make the following changes so you can also use the code in your own Excel worksheet:

• C2:C8: Replace this with the cell range containing cells with zeros.

The above code only sorts the rows containing zeros in a predefined cell range. You must select these sorted rows, right-click, and choose Delete to get rid of these rows.

Conclusion

Finally, mastering the technique to delete rows in Excel when a cell contains 0 is a game-changer for efficient data management.

By following the step-by-step instructions outlined in this Excel tutorial, you can easily declutter your spreadsheets, enhance calculation accuracy, and optimize your data analysis process.

Give the above methods a try and comment below which one you like the most.