View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Confused_in_Houston[_2_] Confused_in_Houston[_2_] is offline
external usenet poster
 
Posts: 15
Default Deleting #NA in a Spreadsheet

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200810


"Confused_in_Houston" wrote:

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.