Hide Rows with #N/A error

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Feb 8, 2011

• #2

Perhaps:

Code:

With Sheets("Sheet1")
    On Error Resume Next
    .Columns("I").SpecialCells(xlCellTypeFormulas,xlErrors).EntireColumn.Hidden = True
    On Error GoTo 0
End With

remember that altering row visibility is a volatile action so you may want to toggle calculation & events as part of this process

(to delete modify .Hidden = True to .Delete)

edit:

You mention multiple columns - if you have some rows that will have errors in Col I but not in the other columns let us know - and detail the other columns

 

Upvote 0

S

sc0ttish

New Member

Joined

Aug 22, 2007

Messages

37

Office Version

1. 365

• Feb 8, 2011

• #3

DonkeyOte said:

Perhaps:

Code:

With Sheets("Sheet1")
    On Error Resume Next
    .Columns("I").SpecialCells(xlCellTypeFormulas,xlErrors).EntireColumn.Hidden = True
    On Error GoTo 0
End With

remember that altering row visibility is a volatile action so you may want to toggle calculation & events as part of this process

(to delete modify .Hidden = True to .Delete)

edit:

You mention multiple columns - if you have some rows that will have errors in Col I but not in the other columns let us know - and detail the other columns

Click to expand...

Hi there,

Thanks, gives me a start.
I've done the following, however I get a compile error so changed the End to End Sub and still get the same error.

Any ideas?

Sub DeleteErrors()
With Sheets("Main Brands")
On Error Resume Next
.Columns("I").SpecialCells(xlCellTypeFormulas, xlErrors).EntireColumn.Delete = True
On Error GoTo 0
End With

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Feb 8, 2011

• #4

The With/End With would appear mid Sub - ie the sample was meant as just a snippet to use in whatever routine you may be running:

Code:

Sub DeleteErrors()
    With Sheets("Sheet1")
        On Error Resume Next
        .Columns("I").SpecialCells(xlCellTypeFormulas,xlErrors).EntireRow.Delete
        On Error GoTo 0
    End With
End Sub

note also it should have read EntireRow rather than EntireColumn - oversight on my part I'm afraid - result of trying to multitask.

 

Upvote 0

S

sc0ttish

New Member

Joined

Aug 22, 2007

Messages

37

Office Version

1. 365

• Feb 8, 2011

• #5

Thanks,

Ended up going with

Sub Kill_NA()
Columns("I:I").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

which runs nice and fast and does the job, cheers again.

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Feb 8, 2011

• #6

It is wise to persist with the Error Handler (and reset) when using SpecialCells

SpecialCells will generate a Debug (if unhandled) should no cells meet specified requirements - eg you run the routine twice in succession (no errors left 2nd time around)

 

Upvote 0

P

prk2022

New Member

Joined

Apr 10, 2013

Messages

44

• Sep 3, 2015

• #7

Hello,

Is there a way to do this with Rows? I have following formula in Column A Row 5 through Row 200. Some records will only return three values and others will return 50 values. Seeing #NUM! is distracting and messy. So I am looking to have all Rows with #NUM! hidden.

{=INDEX('CPU ALL DETAILS July 2015'!$A$2:$A$5000,SMALL(IF(Analysis!$B$2='CPU ALL DETAILS July 2015'!$Z$2:$Z$5000,ROW('CPU ALL DETAILS July 2015'!$A$2:$A$5000)-ROW('CPU ALL DETAILS July 2015'!$A$2)+1),ROW(12:12)))}

So if Analysis!$B$2 only returns 5 records I would want Rows 6 through Row 200 hidden.

Thanks

 

Upvote 0

P

prk2022

New Member

Joined

Apr 10, 2013

Messages

44

• Sep 3, 2015

• #8

Hello,

Is there a way to do this with Rows? I have following formula in Column A Row 5 through Row 200. Some records will only return three values and others will return 50 values. Seeing #NUM! is distracting and messy. So I am looking to have all Rows with #NUM! hidden.

{=INDEX('CPU ALL DETAILS July 2015'!$A$2:$A$5000,SMALL(IF(Analysis!$B$2='CPU ALL DETAILS July 2015'!$Z$2:$Z$5000,ROW('CPU ALL DETAILS July 2015'!$A$2:$A$5000)-ROW('CPU ALL DETAILS July 2015'!$A$2)+1),ROW(12:12)))}

So if Analysis!$B$2 only returns 5 records I would want Rows 6 through Row 200 hidden.

Thanks

 

Upvote 0