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.
- Find and remove all cells with errors by Go To Special
- Find and remove all cells with errors by an advanced tool
- Remove all rows with errors with VBA
- Find and remove all rows with errors by an advanced filter tool
- Other relatived articles (operations) about filtering
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.
3. Click OK, all cells with errors have been selected, press Delete key to remove the errors.
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.
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.
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 Sub3. Press F5 key， then the all error rows have been deleted.
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.
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.
3. Click Ok to add the criterion, then click Filter to filter rows with errors.
Now the rows with errors in column Data have been filtered.
4. Then select these rows one by one, right click to display the context menu, select Delete Row.
Now the rows with errors have been deleted. 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.
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.
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
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!
