A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Deleting #NA in a Spreadsheet



 
 
Thread Tools Display Modes
  #1  
Old October 31st 08, 01:18 PM posted to microsoft.public.excel.misc
Confused_in_Houston[_2_]
external usenet poster
 
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.
Ads
  #2  
Old October 31st 08, 01:39 PM posted to microsoft.public.excel.misc
Gary''s Student
external usenet poster
 
Posts: 11,059
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  
Old October 31st 08, 01:41 PM posted to microsoft.public.excel.misc
John C[_2_]
external usenet poster
 
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  
Old October 31st 08, 01:46 PM posted to microsoft.public.excel.misc
Confused_in_Houston[_2_]
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.

  #5  
Old October 31st 08, 01:47 PM posted to microsoft.public.excel.misc
muddan madhu
external usenet poster
 
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  
Old October 31st 08, 01:58 PM posted to microsoft.public.excel.misc
Gary''s Student
external usenet poster
 
Posts: 11,059
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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
deleting a spreadsheet Amy Excel Discussion (Misc queries) 7 October 30th 08 09:49 PM
deleting rows in a huge spreadsheet with subtotals ExcelNovice Excel Discussion (Misc queries) 7 December 13th 06 07:59 PM
Deleting a Command Button from my Spreadsheet DDrowe Excel Discussion (Misc queries) 2 November 7th 06 02: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 10:19 PM.


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