Seconds to hide rows with #N/A or other specific error values in Excel

In this article, it will provide ways to quickly hide or only display rows which contain #N/A errors or other specific errors in Excel.

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 30 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 30 days.

Hide rows with #N/A or other specific error values by VBA code

In Excel, there is no built-in function can help you, but here is a VBA I provide for you to quickly handle this job.

1. Right click at the sheet tab which contains the #N/A errors you want to hide, then select View Code.
doc hide rows with na 1

2. Then copy and paste below code to the blank script in the popping Microsoft Visual Basic for Applications window.

VBA: Hide #N/A error rows

Private Sub EnableErrorRow()
Dim xWs As Worksheet
Dim xRg As Range
Dim xERg As Range
Dim xEERg As Range
Dim xFNum As Integer
Dim xSreE As String
xSreE = "#N/A"
Set xWs = Application.ActiveSheet
Application.ScreenUpdating = False
On Error Resume Next
    With xWs
        Set xRg = .UsedRange
        For xFNum = 1 To xRg.Columns.Count
            Set xERg = .Columns(xFNum).SpecialCells(xlCellTypeFormulas, xlErrors)
             If (Not TypeName(xERg) = "Nothing") Then
             For Each xEERg In xERg
             If (xEERg.Text = xSreE) Then
                xEERg.EntireRow.Hidden = True
             End If
             End If
        Next xFNum
    End With
Application.ScreenUpdating = True
End Sub

doc hide rows with na 2

3. Press F5 key, then all rows contain #N/A error have been hidden.
doc hide rows with na 3

Note: you can change #N/A to other error you want to hide in the VBA.

Tip: If you want to unhide all ranges, you can try Kutools for Excel’s Unhide All Range utility. Free Download this utility.
doc hide rows with na 4

Only display rows with #N/A or other specific error values by Kutools for Excel

The Super Filter utility of Kutools for Excel provides the options for you to filter specific error values which can only display the rows with #N/A error.

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 data range you use (you’d select including header), and click Kutools Plus > Super Filter to display the Super Filter pane.
doc hide rows with na 5

2. Choose the filter criteria as below screenshot shown:

1) Select the column header you use to filter;

2) Select Error, Equals and #N/A successively in following drop down lists.
doc hide rows with na 6

3. Click Ok > Filter, then only the rows with #N/A error value have been displayed.
doc hide rows with na 7

With Super Filter, you can filter date/year/month/quarter/text format, click here for details.
doc hide rows with na

If the Super Filter cannot gratify you, the Special Filter utility of Kutools for Excel provides some special options for you.
doc hide rows with na 8

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.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases 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 30-day free trial. 60-day money back guarantee.
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.