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.