Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Formula to delete an Observation in excel

Posted on 1998-09-04
13
361 Views
Last Modified: 2010-05-18
Hi,

I have six columns containing data in Excel. How do I program (using VB??) it so that if any of the entries in any of the columns equals "NA", for instance, the whole row gets deleted from the sample.

Thanks
0
Comment
Question by:anony
  • 6
  • 5
  • 2
13 Comments
 

Expert Comment

by:JamesC081498
ID: 1613470
Try this:

Sub RemoveNA()
 With ActiveSheet
  For Each oCell In Intersect(.UsedRange, .Range(Columns(1), Columns(6)))
   If Application.IsNA(oCell) Then
    oCell.EntireRow.Delete
   End If
  Next oCell
 End With
End Sub

This assumes you only want to do this once.  If it has to be a continual check then put the code in Sub Sheet1_Calculate() where Sheet1 is, obviously, the name of the sheet to be checked.
0
 

Author Comment

by:anony
ID: 1613471
Hi James,

How do I incorporate this in Excel? I'm a total novice with VB.  If it is too involved, don't worry about it.

Thanks
0
 
LVL 3

Expert Comment

by:shalbe
ID: 1613472
anony,

Assuming you are using Excel 97 or later, the following are instructions for copying code into Excel. If you are using Excel 5, let me know and I will help you.

In Excel choose Tools / Macro / Visual Basic Editor

In the Visual Basic Editor choose Insert / Module

Copy the code and paste it into this module window.

To run the macro, go back to Excel (make sure the sheet you want to work with is the active sheet) and choose Tools / Macro / Macros.

Select the macro named RemoveNA and click on Run. See if the macro does what you want it to.

Let me know how you make out.

Steve
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:anony
ID: 1613473
Hi Steve,

First of, thanks for the suggestion. Actually, I did try it earlier this afternoon but it doesn't seem to work. I'll keep trying.
0
 
LVL 3

Expert Comment

by:shalbe
ID: 1613474
anony,

I ran a simulation on it and it didn't work for me either.

I will work on a macro for you and get back to you.

I do need to know if the region you are working with (the 6 columns) is a named range and if not, is it contiguous. To test to see if it is contiguous, select a non-blank cell in your data and press Control / Shift / 8. If the entire data set is highlighted, the range is contiguous.

Let me know.

Steve
0
 
LVL 3

Expert Comment

by:shalbe
ID: 1613475
anony,

Try this macro. It should do what you want.

Sub DeleteNA()
    Selection.CurrentRegion.Select
   
    For Each Item In Selection
        If Item.Value = "NA" Then Item.EntireRow.Delete
    Next
End Sub

This assumes the range is contigous and a cell within the data set is selected when you run the macro. If either of these assumptions causes you a problem, let me know and we can resolve it.

Steve
0
 

Author Comment

by:anony
ID: 1613476
Hi Steve,

I'll try it out.

A
0
 

Author Comment

by:anony
ID: 1613477
Hi Steve,

I did try it out. It works. The only problem is that I have to keep running the macro for every individual deletion. Since I have a data set of over 10,000 observations, this makes it very difficult. Is there any way to program it such that it will perform the deletions all in one run?

Thanks,

A

P.S. Please respond by "answering" to the question so that I can give you the points.
0
 
LVL 3

Accepted Solution

by:
shalbe earned 100 total points
ID: 1613478
anony,

It sounds like your data set is not contiguous so the macro is not selecting the entire data set in the first line of code.

Try this macro and let me know if it works. The only catch here is if there is an "NA" outside your data set on the same worksheet, it will delete that row also.

Sub DeleteNA()
         ActiveSheet.UsedRange.Select
         
         For Each Item In Selection
             If Item.Value = "NA" Then Item.EntireRow.Delete
         Next
     End Sub

Any problems, let me know.

Steve
0
 

Expert Comment

by:JamesC081498
ID: 1613479
Just for the record, my solution worked fine on my test data and would work with a non-contiguous dataset.
0
 

Author Comment

by:anony
ID: 1613480
Hi Steve,

How do I modify the program so that I don't have to run the macro over and over again? Since I have a large dataset (over 10,000 observations), I need a macro that does all the line-deletions in one fell-sweep.

Thanks
0
 
LVL 3

Expert Comment

by:shalbe
ID: 1613481
anony,

When I run it on my sample, it deletes all of the rows with NA the first time. There must be something peculiar about your file. If you send me the file I will see why it won't work for you. You can send the file to shalbe@san.rr.com.

Steve
0
 

Author Comment

by:anony
ID: 1613482
THanks, it did work -- I'm not sure what I was doing wrong earlier.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question