Solved

Formula to delete an Observation in excel

Posted on 1998-09-04
13
358 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:anony
Comment Utility
Hi Steve,

I'll try it out.

A
0
 

Author Comment

by:anony
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
THanks, it did work -- I'm not sure what I was doing wrong earlier.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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 …
Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

763 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

7 Experts available now in Live!

Get 1:1 Help Now