How To Remove An Entire Row Based on ANY Condition Without VBA (If Less / Greater Than, Cell Contents, Etc) – Conditionally Delete Rows in Excel

I was trying to remove all rows in a worksheet that were less than 1.00 (Less than $1 profit).

Scoured stackexchange and a bunch of excel forum threads with custom code, I’m still a VBA noob so I couldn’t get any of them to work… *tear*

I randomly came across this video, and found that this nice sounding midwestern lady came up with a simpler solution than all the other programmers forums on the netz.



Problem:

Tons of rows with a profit of less than $1 (including thousands of products that have negative profit). Trying to delete all those rows so I only have useful data left.

Solution:

Use “Delete Duplicates” function in excel to avoid VBA.

Create a new column which uses IF conditional to either throw a TRUE value, or return the unique row number.

Then we simply delete all rows which contain duplicates in the column you select (Column J in my case).

Note: Since it’ll be deleting duplicates that return TRUE, you’ll have 1 conditional left, however it’ll be easy to manually delete.

 

Step by Step Instructions:

1. We will create a new column anywhere to the right of the column we want to sort.
2. The new column will contain an IF statement that will throw a TRUE based on whatever conditional you want (in my case =IF(J1<1,TRUE,Row(J1)) will give a TRUE value if the value in column J is less than 1, and return the row number if cell in column J is >1).
3. We want it to return row number so each value is unique (because we will be deleting duplicates).
4. Select the range of your worksheet, then in Data tab at top click “Remove Duplicates” in Data Tools section at top
5. Click “My data has headers” and click “Unselect All”.
6. Select the column in which you want to apply your condition (For me “Profit”, or column J).
7. Click “OK”. Boom, you’re done.

 

Link to video with step by step instructions: