Skip to main content

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

Author: Sun Last Modified: 2020-11-20

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.

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.

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

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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations