Deleting #NA in a Spreadsheet

[Confused_in_Houston]

I run vlookups and get a large number of #NA responses. I know in a single
column, I can sort by the vlookup column, group the #NA's; highlight and
delete.

However, some spreadsheets have 10 or more columns of vlookups, so I've got
#NA's all over the page. Rather than do ten sorts, I'd like to find a way to
delete all #NA's at the same time.

I've tried the Replace function but it doesn't connect my #NA with the #NA's
on the spreadsheet.

Thanks. I always appreciate the help I get here.

 

[Gary''s Student]

Try this small macro:

Sub nakiller()
Set rna = Nothing
For Each r In ActiveSheet.UsedRange
If r.Text = "#N/A" Then
If rna Is Nothing Then
Set rna = r
Else
Set rna = Union(r, rna)
End If
End If
Next
rna.Clear
End Sub

 

[John C]

Perhaps instead, you could try to solve why you have so many NAs to your
VLOOKUPS ? Or build error trapping into your formulas ?

 

[Confused_in_Houston]

Do I cut and past the exact text below or is some of the text
comments/explanation for my benefit?

I'm not much of a macro writer so if the text below isn't exactly what I
need to enter, then I'm lost. If there are comments, would you please show
me what the macro should look like without any comment text?

Thanks for the response btw....

 

[muddan madhu]

ctrl + F | find what: # | options | look in : values | find all | ctrl
+ A | esc | delete |

 

[Gary''s Student]

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm