Solved

Formula to delete an Observation in excel

Posted on 1998-09-04
13
359 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

948 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now