Deleting rows in Excel based on criteria

Is it possible to automate the deletion of row based on a 
criteria, preferably through a macro?  i.e. - run the 
macro and delete any row who has "NO" in last column.
0
anonymous (74722)
11/20/2003 10:57:47 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
271 Views

Similar Articles

[PageSpeed] 31

>and delete any row who has "NO" in last column.

last column IV ??

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



"SteveM" <anonymous@discussions.microsoft.com> wrote in message news:05ac01c3afb9$b76f96a0$a101280a@phx.gbl...
> Is it possible to automate the deletion of row based on a
> criteria, preferably through a macro?  i.e. - run the
> macro and delete any row who has "NO" in last column.


0
rondebruin (3790)
11/20/2003 11:08:31 PM
I'm not sure what you mean...  

I need to delete an entire row from the worksheet if it 
meets a criteria that I set, i.e. any row that has the 
value "Joe" in column E would be deleted from the sheet....

Thanks

>-----Original Message-----
>>and delete any row who has "NO" in last column.
>
>last column IV ??
>
>-- 
>Regards Ron de Bruin
>(Win XP Pro SP-1  XL2002 SP-2)
>www.rondebruin.nl
>
>
>
>"SteveM" <anonymous@discussions.microsoft.com> wrote in 
message news:05ac01c3afb9$b76f96a0$a101280a@phx.gbl...
>> Is it possible to automate the deletion of row based on 
a
>> criteria, preferably through a macro?  i.e. - run the
>> macro and delete any row who has "NO" in last column.
>
>
>.
>
0
anonymous (74722)
11/20/2003 11:18:06 PM
> value "Joe" in column E
Now I know the column

Example for Sheet1

 Sub Test()
    Dim r As Long
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
        For r = .UsedRange.Rows.Count To 1 Step -1
            If .Cells(r, "E").Value = "Joe" Then .Rows(r).Delete
        Next
    End With
    Application.ScreenUpdating = True
End Sub


-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



<anonymous@discussions.microsoft.com> wrote in message news:041101c3afbc$8da81060$a401280a@phx.gbl...
> I'm not sure what you mean...
>
> I need to delete an entire row from the worksheet if it
> meets a criteria that I set, i.e. any row that has the
> value "Joe" in column E would be deleted from the sheet....
>
> Thanks
>
> >-----Original Message-----
> >>and delete any row who has "NO" in last column.
> >
> >last column IV ??
> >
> >-- 
> >Regards Ron de Bruin
> >(Win XP Pro SP-1  XL2002 SP-2)
> >www.rondebruin.nl
> >
> >
> >
> >"SteveM" <anonymous@discussions.microsoft.com> wrote in
> message news:05ac01c3afb9$b76f96a0$a101280a@phx.gbl...
> >> Is it possible to automate the deletion of row based on
> a
> >> criteria, preferably through a macro?  i.e. - run the
> >> macro and delete any row who has "NO" in last column.
> >
> >
> >.
> >


0
rondebruin (3790)
11/20/2003 11:26:50 PM
Reply:

Similar Artilces:

Are there any household budget templates available in Excel?
Are there any household budget templates available in Excel? http://office.microsoft.com/en-us/templates/default.aspx Do a search near the top for "Budget," and you should find a few of them. Looks like "Personal budget" is the most popular, followed by "Family monthly budget." (based on votes) HTH Dana DeLouis "nevinnh" <nevinnh@discussions.microsoft.com> wrote in message news:2C0B084E-954F-4CAF-8ACB-AF33C6F3F097@microsoft.com... > Are there any household budget templates available in Excel? Go to microsoft template site, on the sear...

Update blank/null fileds with value of previous row using update q
Hi, I need to fill the blank fields with the value of the same field of the previous record. For example : 3 fields in the tableA, 4 records. FIELD1, FIELD2, FIELD3 G5AA,EVA,ABC <blank>,EVB,<blank> G5BB,<blank>,<blank> G5CC,EVC,DEF Expected result : G5AA,EVA,ABC G5AA,EVB,ABC G5BB,EVB,ABC G5CC,EVC,DEF How should I do this? Many thanks. Methinks you are attempting to "commit spreadsheet" upon a table in a Access database. The concept of "previous record" is essentially meaningless in a table in a properly-designed relational database. If...

delete data between subtotals
I would like to delete all data between my subtotals without the subtotals changing. I tried to collapse the whole sheet and to copy the then visible data (with paste values only). this did not work. is there any other way to get rid of data between subtotals? appreciate your help. please see attachement. Attachment filename: illustration_1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=446537 --- Message posted from http://www.ExcelForum.com/ Hi see Peo's response in your original thread -- Regards Frank Kabel Frankfurt, Germany >...

Subfor delete not allowed
Hi All, I know i am missing soething here but i can't seem to explain this condition. The condition exists with all of my forms that contain subforms. A DB table contains 2 fields, School_name and Unit_number (it is intended to track which Scout units draw from which schools) both fields are many to many relationships. A school may have many units drawing scouts from it and unit may draw from many school but no duplicates are allowd. So both unit_number and School_name are joint keys in the School-Unit Table. Each of these is linked to its own tables called Schools (Sch...

reference cell above even if row deleted
I would like to be able to reference a cell imediately above the current cell even if the row above that cell had just been deleted. For instance: contents of A5: =A4+$B$1 then delete row 4 and rather than have contents of A4:#REF!+$B$1 have it A4:A3+$B$1 Is there some way to do this? Thanks for any tips. John Keith kd0gd@juno.com You can use the OFFSET function: =OFFSET(A5,-1,0)+$B$1 John Keith wrote: > I would like to be able to reference a cell imediately above the > current cell even if the row above that cell had just been deleted. > > For instance: > > conte...

Symbols in Excel 2007 Chart
Dear Group, Hello. I would like to use the greek mu symbol in an Excel 2007 histogram. I am able to convert the "m" into the proper "mu" symbol in the spreadsheet itself but this is not reflected in the resulting graphic. I need for my X axis label to be represented by the proper greek letter, not m. Does anyone know how to fix this issue? Thank you in advance. John McLaughlin In many fonts, holding Alt while typing 0181 on the numeric keypad produces �. This means you don't have to change the font for some of the characters in a text element (and many tex...

Excel 2000 tries to open unrecognizable file on startup
Hi, For the last couple of weeks, when I first open Excel I get a Microsoft Excel Error pop-up window that says "This file is not in a recognizable format....". I am uncertain what file it is trying to open, but when I click "cancel" it opens books1.xls. Which I usually ignore and go into "open file" and pick my excel file. Any idea what is going on? Win98 Excel 2000 -- Thanks - Jeff Jeff I suspect one of two things 1) Excel opens all files in it xlStart folder. Search for this and remove anything without an .xls, xlt extension 2) If you go to Tools&g...

Letter Writing Assistant selection based on Smart List Lookup
We had a great opportunity to use the letter writing assistance to send out a notice to any vendor used within the last 6 months. Using the smartlist lookup up we can see those vendors we have issued a check to based on Last Check date, but the Letter Writing Assistant only allows you to select ranges. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the ...

"10th" on Excel
For the past few weeks, any time I enter the number "10", the Excel program automatically adds "th" making it "10th". This is also occuring on the Word program. I don't remember making any changes to either toolbar- HELP!!! I can think of one possibility, (and don't ask me how it may hav happened), but look in Tools - Autocorrect and see if you can find "10 shown as being replaced by "10th". My program once started replacin all lower case "p"s with upper case and I know it was nothing I did. If that isn't the problem, I ca...

combine multiple excel file in to one excel file and multiple worksheet
I am wanting to use the following code to combine worksheets from multiple files. However I would like to be able to select folder which contains files in a more automated way that having to change the code every time, and also copy all worksheets with links and formulas removed. Any help on this is greatly appreciated as I have limited code knowledge. Sub Copy_them() > Dim TargetWkbk As Workbook > Dim mrgWkbk As Workbook > Dim i As Long > Dim Wks As Worksheet > Dim fName As String > Application.ScreenUpdating = False > Set TargetWkbk ...

Automatic backup in excel with copy to hard drive and to cd-rw
How do I get Excel to either automatically backup to hard drive and cd-rw, or even when I hit save icon, go ahead and save to hard drive and cd-rw. I already have auto save setup but backup file goes to same hard drive folder as origianl copy. Kind of defeats the purpose of a system crash and wanting a copy on removable media. Thanks for your help! This Word macro auto-saves a copy to a floppy drive. You should be able to edit it to save to the hard drive and CDRW, too. You'll also change some stuff to make it an Excel macro: http://www.vbaexpress.com/kb/getarticle.php?kb_id=203 *...

Why does Excel ask to save when no changes were made?
This does not pertain to a new document. I open an existing document just to look at it, and when I go to close it, Excel asks if I want to save the document. No changes were made, so it shouldn't have to be saved. It's annoying. Mike One explanation could be that there is a time formula in a cell that has updated on openeing so the worksheet will have changed. -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''''s razor (Abbrev) &...

Can administrator empty Deleted items in each mailbox
Hi, Is there any chance that administrator can delete all items in Deleted items folder? Tx, Gasper Are you talking in each user's mailbox or the deleted items available for restore? What version of Exchange? -- Regards, Hank Arnold "Gasper" <NOSPAMgasper.derzanic@tipro.si> wrote in message news:tAfEb.108$%x4.10122@news.siol.net... > Hi, > > Is there any chance that administrator can delete all items in Deleted items > folder? > > Tx, > > Gasper > > > > Is it possible to delete Deleted items in each user mailbox on Exchange ...

Excel & Word auto selection from menus
Ok, I'm a keyboarder, not a mouser. I use quick commands like the letter code or arrows on menus to speed along without the grab, aquire, click business. Excel & Word seem to be periodically (not always) automatically selecting menu items based on prior selections if I use arrow selectors. How do I turn this off? I get no help from "Help". Please reply to my e-mail address: legreenwood@hotmail.com. Thanks! Would love to get rid of this nuisance & keep speeding along. ...

My excel file is too large (5 mb) when it shouldn't be, why?
I have an excel file has ballooned in size for some reason. I have tried to copy all the cells and then used "paste special" to paste just the values and the file size doesn't reduce. I am copying numbers from an investment website into my spreadsheet and there don't appear to be any issues with the pasted cells but I suspect it is causing the file size expansion and very slow performance. I then deleted all the cells around by spreadsheet and it didn't help at all. All suggestions as to how I can fix this problem appreciated. Kenview If you do a Ctl-End, does...

select query of parent and child in one row
I have a parent table and a child table one parent has between 1 and 6 children in the child table I wont a query to have the parent and the child in one "row" how do I create that? thanks for helping me Your table structure is not correct for a relational database but this will get you there. This has three but you get the idea and expand on it. Place the table in the design grid as many times as you have field to combine. SELECT MultipleFieldData.x, MultipleFieldData.a & IIf(MultipleFieldData_1.b Is Null,Null," " & MultipleFieldData_1.b) & IIf(MultipleF...

Creating Excel Invoice Template
I have been trying to customize a downloaded template but was frustrated so trying to create my own invoice. My question is once I input the formulas how do I keep/save them ???? when I enter the first calcualtions all is well, but if I go back and edit in the cell where the formula resides I lose the formula ??? I am a relative newbie to this so any help will be greatly appreciated. I have searched this site and cannot find a link to creating an invoice worksheet. Sasha ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions wit...

Sending Email in Excel
1.) I have a formula in a cell that when clicked, sends an email to recipients in the formula. I have pasted the formula below. What I want to do is put the date after the "End of Day: " part. However, when you click on the link, it gives the serial representation of the date if you use today(). Is there anyway to format so that is shows the date formatted correctly? In cell A1: =HYPERLINK(CONCATENATE("mailto:Person1@abc.com; Person2@abc.com?Subject=End of Day - ",A34,": Acc: ",AB1," for ","$",AA1," | Feats: ",'Enh Feats'...

can not delete in outlook
When I try to delete anything out of outlook i get the following errror The messaging interface have returned an unknown error. If problem presists restart outlook Sam wrote: > When I try to delete anything out of outlook i get the > following errror > > The messaging interface have returned an unknown error. > If problem presists restart outlook OL2000: Error Message: The Messaging Interface Has Returned an Unknown Error http://support.microsoft.com/?kbid=275186 -- G�rard gj.oomens@somso.demon.nl ...

Deleting Previous Addresses or Email accounts
from the Email Server. Can some one help me? We are currently running MS Exchange along with Windows 2000. How do I delete e-mails from previous employees? The problem is that we continue getting junk e-mails with the names of those employees. Your help will be greatly appreciated. Tazzie Tazzie <anonymous@discussions.microsoft.com> wrote: > from the Email Server. Can some one help me? We are > currently running MS Exchange along with Windows 2000. How > do I delete e-mails from previous employees? The problem > is that we continue getting junk e-mails with the names ...

Excel 2000: Regional date problem
We'e just upgraded from Excel 97 to 2000 and are having problems because dates in spreadsheets are showing in US (mm/dd/yy) format rather than in European (dd/mm/yy). This si true whether I use a standard date format or make a custom format. I've checked the regional settings in Windows 2000, and they are correct. I thought Excel picked up the default from here. Any other ideas where I might look? Thanks in advance Alan I wasn't able to duplicate your problem, Alan. I switched to UK settings through Regional Settings. Then I opened Excel 97 and entered =NOW() in two ce...

unable to delete online payees money 2007 deluxe
when i try to delete online payees i get a message that says " money cannot delete this payee because the payee has scheduled or recurring payments". in fact, there are no scheduled or recurring payments so there must be a glitch in they system. does anyone know how to fix this problem? i have spoken w/ my bank about it and they don't have a clue. On Mon, 29 Mar 2010 15:20:05 -0700, kate <kate@discussions.microsoft.com> wrote: >when i try to delete online payees i get a message that says " money cannot >delete this payee because the payee has s...

Excel countif and
is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1$3371a4f0$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

Percentage formula in Excel spreadsheet
Hi, In my spreadsheet cell E14 adds up E2 - E14 and cell C14 adds up C2 - C14. In G14 I have put a formula to show the difference between E14 and C14 as a Percentage ( formula for G14 is =SUM(E14-C14)/ABS(E14) ). When the Formula for G14 looks at E14 and C14 all it sees is a SUM formula to add up the column. Does anyone know how to solve this problem. Many thanks for looking. Rick Not sure I understand the problem; tell us what answer or error you ar getting. Also, don't you mean you are summing C2:C*13* in C14 and E2:E*13* i E14? Oh, and you can get rid of the Sum - this ...

Excel macro error msg..
Hi I get the following msgbox when I attempt to run a Macro - which is just a simple text string to be put into a cell. " 'xxx'.xls could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open a file from your list of most recently used files on the file menu, make sure that the file has not been renamed , moved or deleted." (ok) 'xxx.xls' trefers to a file name that was on my disk - but I deleted - I can't find it after a search. All macros when run, exhibit the same thing..... Tried a goog...