Windows  |  Free Downloads




Delete Rows Based on Criteria in Microsoft Excel

Suppose you have to delete all the records that contain an N in the Y/N column in the spreadsheet in the graphic. You could use AutoFilter, but that won't always be possible.

If you have to do it often to the same data layout, you might want to use a VBA procedure. But if you're in a hurry and never used VBA before, that might not be practical.

Normally, we would just sort by column D, and delete all the rows that contain N. That's the quick way. But sometimes, we can't sort because it'll mess up our sort order, and we can't sort it back. So we create a helper column. Click on column A (or anywhere is fine as long as it is adjacent to your data, with no blank columns in between), and hit InsertàColumn. Give the column a heading in cell A1. Type in 1, 2, 3 in cells A2, A3, and A4 and then select those 3 cells and drag down to number (in ascending order) all the records in your worksheet.

Then sort by the necessary column. In our sample, it's column E. We select all the rows with N in column E, and then EditàDelete.

We are left with only the records that have Y in column E.

We sort again by column A (our helper column). We delete column A. debuted on May 26, 2005. provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2014 by MrExcel Consulting | All rights reserved

privacy policy