5

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!

2
  • 2
    Select the entire set of cells - apply autofilter
    – Anonymous
    May 26, 2011 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, 2011 at 10:47

5 Answers 5

2

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'.

1
  • OK, this preserves the original row order.
    – mousio
    May 26, 2011 at 12:27
2

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

1

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.

1

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.

0
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .