Skip to main content

How to quickly find and remove all rows with errors in Excel?

Author Sun Last modified

This article will introduce the methods on quickly getting the list of last or current opened Excel files for you.


Find and remove all cells with errors by Go To Special

In Excel, you can apply Go To Special function to find the cells with errors, then delete the cell contents.

1. Select the range or entire sheet, then press Ctrl + G to enable the Go To dialog.

2. Click Special to the Go To Special dialog, then check Formulas option, under Formula option, check Errors checkbox only.
doc find remove errors 1

3. Click OK, all cells with errors have been selected, press Delete key to remove the errors.
all cells with errors are selected, press Delete key to remove the errors


Find and remove all cells with errors by an advanced tool

If you want to quickly find and remove all cells with errors, here is a tool in Kutools for Excel can help you to solve this job with clicks.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the range which you want to find the error cells, click Kutools > Select > Select Cells with Error Value.
click Select Cells with Error Value feature of kutools

2. Now the all error cells have been selected, click OK to close the popping remined dialog, then press Delete key to remove the error values.
all error cells have been selected
press Delete key to remove the error cells


Remove all rows with errors with VBA

In Excel, there is a VBA code can remove all rows with errors at once.

1. Press Alt + F11 key to enable Microsoft Visual Basic for Applications window, click Insert > Module to create a new Module script.

2. Copy and paste below code to the script.

VBA: Remove error rows

Sub DeleteErrorRows()
Dim xWs As Worksheet
Dim xRg As Range
Dim xFNum As Integer

   Set xWs = Application.ActiveSheet
Application.ScreenUpdating = False
    On Error Resume Next
    With xWs
        Set xRg = .UsedRange
        xRg.Select
        For xFNum = 1 To xRg.Columns.count
            With .Columns(xFNum).SpecialCells(xlCellTypeFormulas, xlErrors)
                .EntireRow.Delete
            End With
        Next xFNum
    End With
Application.ScreenUpdating = True
End Sub

3. Press F5 key, then the all error rows have been deleted.


Find and remove all rows with errors with Super Filter

The Super Filter utility of Kutools for Excel can filter rows with errors then you can delete them.

After free installing Kutools for Excel (30-day free trial), please do as below steps.

1. Select the data range you use, click Kutools Plus > Super Filter to enable Super Filter pane.
click Kutools Plus > Super Filter

2. In the Super Filter pane, add the criterion as below:

1) Select the column header you use to filter;

2) Select Error from second drop-down list;

3) Select Equals from third drop-down list;

4) Select All Error from last drop down list.
add the criterion in the super filter pane

3. Click Ok to add the criterion, then click Filter to filter rows with errors.
click Filter to filter rows with errors

Now the rows with errors in column Data have been filtered.
the rows with errors in column are filtered

4. Then select these rows one by one, right click to display the context menu, select Delete Row.
select the filtered rows and click Delete Row

Now the rows with errors have been deleted. Click Clear button in the Super Filter to display other rows.
Click Clear button in the Super Filter to display other rows

Tip: With Super Filter pane, you also can filter #NAME? error or #DIV/0 or other specific error only.
 filter #NAME? error or #DIV/0 or other specific error only by kutools

Super Filter is a professional filter tool which can filter rows in multiple criterion and provides the criterion Excel cannot support. Click for details.


Other Operations (Articles) Related To Filtering

Filter data based on a list
This tutorial provides tricks on filtering data based on a given list data in Excel.

Filter data contains asterisk
As we know, we use asterisk mask to stand any characters when you filter data, but how can you do if you want to filter data which contains asterisk mask? Now this article introduces the method about data filtering if containing asterisk or other special characters in Excel.

Filter data with criteria or wildcard
If you want to filter data with multiple criteria, how can you do? This tutorial is talking about how to set multiple criteria and filter data in Excel.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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!