Deleting Records based on criterion

I know about advanced filters and filtering out records 
based on a criterion.

But if I wanted to use the approach of deleting the 
records that don't match a criterion, how would I go about 
this in Excel?

I would rather not use a macro for this unless its the 
only way.

Thanks,


Bruce
0
broberson (13)
9/18/2003 2:19:14 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
315 Views

Similar Articles

[PageSpeed] 16

After you do your Autofilter (or in-place Advanced Filter) only the rows to
be deleted will be visible. Select these rows (you only need to select one
column) and do an Edit, Delete Row.  Click Ok to the confirming prompt.
Only the visible rows will be deleted (Excel is pretty smart<g>) just as you
wanted.

-- 
Jim Rech
Excel MVP


0
jrrech (1933)
9/18/2003 3:55:49 PM
Reply:

Similar Artilces:

How to delete or permantly change logo in personal settings
When I was just learnng how to do publisher I created a logo and Iwant to change it in the permanant settings for the personal information. I cannot delete it or edit it. Also on the other 3 settings for secondary, home etc. it does not alowany logo at all. Anyone know? Locate the following files and delete them: Primary Business - biz1logo.jsp Secondary Business - biz2logo.jsp Other Organization - Orglogo.jsp Home/Family - Perslogo.jsp -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no ...

Limiting formulas based on filtering?
Is there an easy way to build a formula that will only take values into consideration when the auto format is used. So, if I have a table of data and a formula based on this table and I filter one of my data columns to some vause then I would like my formula to automatically update. Is this possible? Thank you, CH The SUBTOTAL function ignores rows that are hidden by an autofilter (and optionally, also rows that were hidden manually). It can perform a wide range of operations, including sums, averages, counts, standard deviations, products, maximums, minimums, etc. Check E...

Change cell background color based on content that results from li
This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it reall...

Delete partial string
Hello! I have a column with strin in cells 26-02-2004 09:27 jhfkfhg fhgfjfg 26-02-2004 10:28 ewqteter rerewewtrre I know delete date/hour group with Replace, but I need inverse. Preserv date/hour group and delete only alfanumeric string. Is possible in Excel? Thanks in advance. an Hi one way (if your data is always structured as below) - add a helper column (lets say B) - insert the following formula in B1 (if column A stores your data): =LEFT(A1,FIND(":",A1)+2) copy down - now you can copy this row and insert it again with 'Paste Special - Values' -- Regards Frank K...

create xml document based from schema
I have the following schema. I need to know how to make an xml document based from it. The main worry I have is the DateTime field. <?xml version="1.0" encoding="utf-8"?> <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="NewsArticle"> <xs:complexType> <xs:sequence> <xs:element name="Description" type="xs:string" /> <xs:element name="Body" type="xs:string" />...

enter value based on drop down list of another field?
I have an excel 2003 spreadsheet with 3 worksheets. In sheet 1, I created a drop down list in column A based on values in sheet 3, column A (item number). Thisd data comes from sheet 3, where I have column A (Item #'s), column B (Item desc)an Column C (price) In column B of sheet 1, I have Item Descriptions and in column C I have price. I want this to automatically fill in based on what I enter in the column A sheet 1 and it should get the corresponding values from sheet 3 column B and C. I can make the drop down list in sheet 1 column B and C, but some of the descriptions need ...

Reports based on groups
I am using access 2007 and need to create individual reports for a number of different client records so I can export them to email to the clients. The number and names of clients vary each month so I want to write either a macro or module or something such that it will automatically generate the reports each month. Thoughts? ...

deleted document
I have deleted an important document and need to retrieve it. The document deleted was on our server and I don't know how to find it and restore it. Contact your IT staff and ask them to restore it from the last backup. But try to find out what share it was on (and folder and filename). It might mean a quicker response. dcalhoun wrote: > > I have deleted an important document and need to retrieve it. The document > deleted was on our server and I don't know how to find it and restore it. -- Dave Peterson Hope your network administrator does regular backups (like da...

Remove records from lookup
Not sure if this is possible but I would like to remove all records except the account records from the look up records dropdown menu of the appointment entity. The associated schema is regardingobjectid and the label is regarding. I see the other entities listed under relationships but there is no option to delete. Is there a way to create a new lookup and have it added to my appointment form? Thanks. There is no supported way to change this. The system is setup to allow you to set appointments that are related to specific records (Opportunities, Quotes, Cases, etc) and it will then ro...

Getting back a deleted test frame after saving the changes
My son just deleted a text frame with all his work and started a new text frame and then when asked if he wanted to save his work he clicked yes. Is there any way to retrieve that missing text frame when it won't allow you to undo? On Sat, 27 Aug 2005 16:04:01 +0100, Denise wrote (in article <09056FDE-03FC-4988-8AB0-D1EA0896BFC7@microsoft.com>): > My son just deleted a text frame with all his work and started a new text > frame and then when asked if he wanted to save his work he clicked yes. Is > there any way to retrieve that missing text frame when it won't a...

Deleting Blank lines with VBA
Help I have a macro that runs multiple formulas. However, when the formulas are finished I am left with about 64000 blank lines. I am unable to insert a row on the worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank Cell using CTRL-END). Here is a same of some of the formulas I am running ( the macro is very large). ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27" ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28" ActiveWorkbook.Names.Add Name:="TrunkFormulaK&qu...

Deleting a excel file
Thanks for reading. I'm on a network at work, no signing in needed on the computer. Is there a way I can make a Excel file or folder and have where others can't delete it? I know how to protect it where the file can't be changed, but sometimes someone will delete it. I know this is the Windows 2000 security question but hope to fine the answer here, I'm lost. Thanks Much Bob Talk to your IT staff. Ask them to give you a share or folder on that network that only you (and a trusted co-worker) have write access to. But make...

Still recieving e-mail for deleted users
We are receiving e-mails for users that were been deleted from our server years ago. Does anyone know how to block these e-mails. We have a SPAM program that allows you to blacklist but it does not stop the messages from coming through. Create ea distribution list containing no members, but which has all the long-gone users' smtp addresses. This is called a blackhole DL. Exchange will accept the mail but silently drop it - no NDRs, no storage. -- hth, SusanV "RB" <pr@epley-pr.com> wrote in message news:574a01c40071$05a154a0$a501280a@phx.gbl... > We are receiving...

How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...

ACCESS 2000 is deleting records
Just yesterday I converted an Access 97 database to Access 2000. Have a large problem I need help with. After converting the DB I "split it" in order to use it as a backend, same way it was in Access97. After converting the database to Access2k we began to have a problem with Access2k deleting records. it deleted a total of 4 out of 62 records. the records are not together ( record #289365 then 289048 then 289128 then 289178 then this morning 289405) these record are auto-numbered incerement of one. They were entered by different people so I know its not just an operator probl...

Can't Delete Rules
I have a user with some rules but the rules can't be deleted. I got two error message and one is "The rules on this machine do not match the rules on your exchange server" and ask "Which rules do you want to keep" Client, Server, Cancel. I can delete Client rules and also Server rules, but they are all back once I restart Outlook. The other error message is "one or more rules could not be uploaded to Exchange server and have been deactivated." Is there any way I can remove all of the rules? Thanks for your comments in advance. Start-> Ru...

Office X 'clean' reinstall: delete all old files?
I'm about to try installing the latest bug-fix collection, sorry. "security update" for Office X, 10.1.6. I'm hoping it might reduce the frequency of Word's charming crashes. Who knows, I might get lucky and it'll even fix the completely messed up 'contact' feature in Word that is grayed out most of the time, but when it decides to work, will only list the 'A's' from my address book. Just useless. Anyway, back to the plot: since my Office X is so bent out of shape, I figure I'll do a 'clean install' from the original disk first, then...

Comparing cell values then labeling them based on comparision
Hi. I'm trying to find a function or way to compare two cells and then label them according to if one cell is lesser, greater, or the same as the other. I have a large data sheet where I want to compare many pairs of cells and then have them labeled in this way. For example for each pair where the second cell value is larger than the first it would put greater, where the first is larger it would be lesser, etc. Can anybody help me with this? also feel free to email me at andrew@metiri.com. Thanks so much for your help. > Hi. I'm trying to find a function or way to compar...

query to make a list of products based on delivery history
I have a table which lists all deliveries made of our product. From this table, I'd like to make a list of all products. My problem is the products will have many duplicates as they can be ordered multiple times and I just want a list showing all the individual products that we offer. -- TIA Try something like: SELECT DISTINCT [our product] FROM [which list all deliveries made]; If you can't figure this out, come back with table and field names. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Def...

importing excel spreadsheets into web-based applications
How do you automate importing data from an excel spreadsheet into a web-based applications. For example, a form on the web has fields for name, address, phone, email, etc. (some of the fields are required, some optional) and you have a spreadsheet with information for a 100 different people (or even 1 person), how do you fill in all the fields at once without having to cut and paste each field for each person. ...

Update Query based on Current Recordset
I have a continuous form with filtered records. I want to run an update query on a field in a table based on the current filtered records on the form. What code would I use to accomplish this task? Any help appreciated in getting me started. NEWER USER, Without a wee bit more information, like what are you trying to update, field names, table names, do you want this attached to a button or an event, etc... The best I can do is... UPDATE SomeTable SET SomeTable.SomeField= WhateverValue WHERE (((SomeTable.SomeOtherFied)=WhateverValue)); Of course, the above would be an ...

Programmatically adding function to copy all incoming mail to public folder (based on sender domain)
Hi all. I would like to be able to (from any client) execute a script (vbs, exe, whatever) that creates a public folder. That's should be pretty easy. The tricky part is that I want the same script to create a rule (?) on the Exchange server that copies all incoming mail from a specific sender domain to that new public folder. Hmm... Let us assume that I have the sufficient credentials for this action hardcoded in the script.. does anyone have a good idea how to do that. I am creating an interface in our ERP software that should be able to see mail correspondance horizontically accross ...

delete dupl from 1 table that match table 2
I have 2 tables joined by an acct # but diff data in the 2 other columns. I want to delete the row of information from table A that have a matching acct # in table b. ie: appl acct # amount (table a) appl acct # amount (table b) b 1234 $1.00 b 1234 $5.00 c 111 $1.00 c 12345 $5.00 c 1001 $2.00 c 1001 $3.00 want to delete rows from table a. for accts #1234 & #1001 DELETE [Table A].[Acct #] ...

deleted or filed emails keep reappearing into inbox
Hello, I have a user that when ever he deletes an email or even if he files an email and moves it to a folder, a few days later that email keeps coming back to his inbox. He is using Outlook2007, is connected to Exchange 2007 and he's also a blackberry user on BES 5.0. I have tried the following: - recreating his profile in Outllook , -checked on his blackberry to make sure Mailbox wins on conflicts,- - he has no rules in Outtlook -turn on/off the Cashed mode Any ideas? ...

Insert query with two left outer joins gives "Record is Deleted" m
Hi, I have built an insert query to combine data from 3 tables into one table based on a key value (RA_ID) on a form. Two of the from tables may or may not have data associated with the main from table. So I have coded the select from statement using left outer joins. When I run the query with a row in the first child table but not in the second it works fine but when I run the query with a key value that exists in the 2nd child table but not in the first I get a message saying "record is deleted". Anybody have any ideas what is causing this? Here is the query......... ...