Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting a spreadsheet | Excel Discussion (Misc queries) | |||
deleting rows in a huge spreadsheet with subtotals | Excel Discussion (Misc queries) | |||
Deleting a Command Button from my Spreadsheet | Excel Discussion (Misc queries) | |||
Excel 2003; spreadsheet with filtering; deleting rows | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel |