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
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.
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com 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