Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a list of hundreds of users in an Excel spreadsheet. I want to remove all of the users that don't have their email address in there spreadsheet entry. there's a column called 'email', so how would I remove all the rows with a blank 'email', without having to go through every entry and delete each one manually.

Bear in mind I'm a complete newbie on Excel; I'm not sure how to run code. Some pointers would be very helpful!

share|improve this question

migrated from stackoverflow.com May 26 '11 at 12:45

This question came from our site for professional and enthusiast programmers.

2  
Select the entire set of cells - apply autofilter –  Anonymous May 26 '11 at 10:46
2  
If you sort by email all the empty ones will be grouped together so can be deletead at once –  Alex K May 26 '11 at 10:47
add comment

5 Answers

up vote 2 down vote accepted

Select the 'email' column and go Data > Filter > AutoFilter to add a filter to that column. That will add a dropdown menu to the column (a little triangle). From this filter you can choose to show only the empty rows. Then you can select them all and delete by right-clicking on the row label and selecting 'Delete Rows'.

share|improve this answer
    
OK, this preserves the original row order. –  mousio May 26 '11 at 12:27
add comment

Without running code, the next easiest thing would be for you to sort the rows by email address and delete the users that do not have an email address. This will at least put all of them together and make the manual process less painful.

share|improve this answer
add comment

Select the entire set of cells - apply autofilter. Filter on email = blank Select all filtered rows and delete them.

share|improve this answer
add comment

I would assume your data has headers.

  1. Highlight all of the columns in your data.
  2. Press Ctrl + Shift + L to apply filtering.
  3. Go to the Email column. Click the arrow and then select Blanks.
  4. Highlight all of your data (except the headers).
  5. Press Ctrl + G, click Select and then choose Visible cells only.
  6. Press Ctrl + Minus to delete the rows.

Steps 4-6 are important if you want to make sure you don't lose all of your data. Excel may also delete cells that are hidden through filtering.

share|improve this answer
    
Excellent answer. Thanks. –  user1477388 Apr 15 at 14:43
add comment

Data - Filter - Auto filter.
Email column dropdown: (Blank)
Select all rows. Right-click. Delete rows.
Email column dropdown: (All)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.