[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

Formula to delete an Observation in excel

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
anony
Asked:
anony
  • 6
  • 5
  • 2
1 Solution
 
JamesC081498Commented:
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
 
anonyAuthor Commented:
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
 
shalbeCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
anonyAuthor Commented:
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
 
shalbeCommented:
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
 
shalbeCommented:
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
 
anonyAuthor Commented:
Hi Steve,

I'll try it out.

A
0
 
anonyAuthor Commented:
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
 
shalbeCommented:
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
 
JamesC081498Commented:
Just for the record, my solution worked fine on my test data and would work with a non-contiguous dataset.
0
 
anonyAuthor Commented:
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
 
shalbeCommented:
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
 
anonyAuthor Commented:
THanks, it did work -- I'm not sure what I was doing wrong earlier.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now