Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Formula to delete an Observation in excel

Posted on 1998-09-04
13
Medium Priority
?
371 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
[X]
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
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 400 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 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