Reply
 
LinkBack Thread Tools Display Modes
  #1   Report Post  
Old October 31st 08, 12:18 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 15
Default Deleting #NA in a Spreadsheet

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.

  #2   Report Post  
Old October 31st 08, 12:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default Deleting #NA in a Spreadsheet

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.

  #3   Report Post  
Old October 31st 08, 12:41 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 1,358
Default Deleting #NA in a Spreadsheet

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


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

  #4   Report Post  
Old October 31st 08, 12:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
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.

  #5   Report Post  
Old October 31st 08, 12:47 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 747
Default Deleting #NA in a Spreadsheet

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



On Oct 31, 5:18*pm, 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.




  #6   Report Post  
Old October 31st 08, 12:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default Deleting #NA in a Spreadsheet


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

--
Gary''s Student - gsnu200811


"Confused_in_Houston" wrote:

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.



Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
deleting a spreadsheet Amy Excel Discussion (Misc queries) 7 October 30th 08 08:49 PM
deleting rows in a huge spreadsheet with subtotals ExcelNovice Excel Discussion (Misc queries) 7 December 13th 06 06:59 PM
Deleting a Command Button from my Spreadsheet DDrowe Excel Discussion (Misc queries) 2 November 7th 06 01:49 PM
Excel 2003; spreadsheet with filtering; deleting rows MHcoTech Excel Worksheet Functions 0 September 6th 06 01:33 AM
how prevent formula in cell from deleting when deleting value???? sh-boom New Users to Excel 1 September 30th 05 06:12 PM


All times are GMT +1. The time now is 11:57 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017