Delete selected rows from Excel

Posted on 2011-04-27
Last Modified: 2012-08-13
I have number of spread sheets and I want to delete records from them.

Example :
I have following data in spreadsheet:

Name                                                  Id
GenePoint                                  001E0000002eoqCIAQ
United Oil & Gas, UK                 
United Oil & Gas, Singapore         
Edge Communications                  001E0000002eoqFIAQ

what i want to do is : i want to delete records which don't have ID. i.e United Oil & Gas, UK                 
United Oil & Gas, Singapore . also i dont want to delete header fields that is Name and id.
how can i do it ??
Question by:siddharthkool
    LVL 85

    Assisted Solution

    by:Rory Archibald
    You could just sort the date on the ID column and then delete all the blank ID rows from the bottom?
    LVL 6

    Assisted Solution

    sort your spreadsheet by the id then select those rows as they will be in sequence of each other. then delete the rows.
    Afterwards you can re-sort which ever way you need it.
    LVL 31

    Accepted Solution

    If you don't want to affect the existing sort order you can use the AutoFilter option.

    Select all the data, including headers, and Select the AutoFilter from the Data menu.

    Use the dropdown on the ID column to select Blanks. This will hide all rows with an ID. Highlight the visible rows and do what you would normally do to delete rows. An error message will come up about deleting Entire Row, click OK.

    Blank rows now gone and no data visible. Use the dropdown on the ID column to Show All.

    Job Done. If you want rid of the drop downs, deselect the AutoFilter option form the Data menu.

    Rob H
    LVL 31

    Expert Comment

    by:Rob Henson
    Alternatively, use the AutoFilter to show NonBlanks and copy the result elsewhere. Only visible rows will be copied but formulae driven cells will be converted to value.

    Rob H
    LVL 24

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    728 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

    18 Experts available now in Live!

    Get 1:1 Help Now