Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

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

Error Condition Wizard

If there are multiple types of errors in a sheet, and you want to convert these errors to empty or other values, you can apply Kutools for Excel's Error Condition Wizard to quicky handle this task.
Click for 60 days free trial!
doc ignore errors 3
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

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.
doc find remove errors 2


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.

Boots Your Excel With One Excel Suit
300+ professional and easy-used tools for Excel 2019-2003

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

1. Select the range which you want to find the error cells, click Kutools > Select > Select Cells with Error Value.
doc find remove errors 3

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.
doc find remove errors 4
doc find remove errors 5


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 (60-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.
doc find remove errors 6

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.
doc find remove errors 7

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

Now the rows with errors in column Data have been filtered.
doc find remove errors 9

4. Then select these rows one by one, right click to display the context menu, select Delete Row.
doc find remove errors 10

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

Tip: With Super Filter pane, you also can filter #NAME? error or #DIV/0 or other specific error only.
doc find remove errors 12

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


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.


Kutools for Excel - The Best Office Productivity Tool Increase Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 60-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.