In this blog, I will discuss how to delete rows in excel based on the condition. Sometimes we get the data set from another person and the need to do a lot of work to clean this data set.
You need to delete a certain section of data that is not relevant or there are innumerable blank cells, which you need to delete to analyze the data. If you don’t know the trick then it will take a lot of time to complete the task, but if you know the trick then you can do it in a quick span of time.
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
Conditionally Delete Rows in Excel
We will discuss how to delete rows in excel based on certain condition:
1. Delete the entire row based on No value in Excel
If you have a datasheet containing the value of clients as Yes and NO. This method is used to delete the row in which the client’s value is no and keep the yes value clients.
For example, column ‘E’ have values of Yes and NO then,
- Step 1: Create another column to keep track
- Step 2: Insert new Column ‘F’
- Step 3: Fill the column with serial numbers 1, 2, 3, …
- Step 4: Select entire data by Ctrl + A
- Step 5: Click on Go To Data > Sort By > Column E
- Step 6: Click OK
- Step 7: You will see No and Yes are grouped apart from each other.
- Step 8: Again go to sort, set sort by to Help Column and order to Smallest to Largest.
- Step 9: Click OK
You will get the desired result of deleted no clients and only Yes clients will be reflected on your datasheet. But these are short steps, please read the below paragraph for more information.
For example, you have a data set wherein a column, yes and No values are mixed up. And you need to delete the client where no value is there. We can definitely sort this column and then we can delete the rows where the value is only no. But you can not always use the sorting in excel option as it will mess with the order. So what is the best possible to remove the client’s name where it says no? Here we go…
For example, you have the dataset like below where column E indicates Yes and No and you need to delete the rows where it is No. You can use auto filters also but sometimes it also not possible.
Now you will create another column which will help us to track the order. You need to insert a column and then fill it with a serial number like 1, 2, 3…
In column F, I have placed the serial number so that the order cannot be messed up. Now I select the entire set of data by pressing ctrl +A and then go to data and I will sort the data and the sort by column should be column E which indicates Yes/No.
If you click on ok then you will see that all the no values on one side and yes values are on another side. Then you need to select the entire rows for “No” one only and then you can right-click and press the delete. It will help to delete the entire row where the values are no. Or you can go to the Home tab and then click on Delete icon on the cell section and then press delete sheet rows.
Now after pressing the delete button, all the clients will be deleted from the sheet where the value was No in your Yes/No column. Till we are not finished. Your order is now messed up.
Now you sort again and this time, your sort by column should be Help column and order should be smallest to largest.
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
As soon as you will press the ok button your data will be back in the same order as it was earlier. The serial number for No values obviously should be deleted. Your data now looks like below.
Now you delete your help column to get back the original set of records but here from your “No” is deleted. And the important part is that your data order remains the same.
2. Delete unwanted multiple rows using Filter
Follow the following steps:
- Click on any cell inside your dataset and then press Ctrl + A or Ctrl + *
- To apply the filter to a selected range of cells, select Data Tab >> Filter
under Sort & Filter Group or press Ctrl + Shift + L - Select the “No” value inside Column E’s dropdown menu
- The filtered table with only “No” in Column E can now be selected
- Select all filtered Entire Rows and then press Ctrl + –
- All Rows will be deleted
- Now remove filter from your dataset
- Here you go… All unwanted data has been removed
3. Delete Row Based On Cell Value in Excel
Here are the simple steps to delete rows in excel based on cell value as follows:
- Step 1: First Open Find & Replace Dialog
- Step 2: In Replace Tab, make all those cells containing NULL values with Blank
- Step 3: Press F5 and select the blank option
- Step 4: The Right Click on active Sheet and select delete rows.
- Step 5: It will delete all those rows based on cell value of containing word NULL.
4. Delete an entire row with Find Option in Excel
Follow the following steps:
- Step 1: Select your Yes/No column
- Step 2: Press Ctrl + F value
- Step 3: Search for No value
- Step 4: Click on Find All
- Step 5: Select all by pressing Ctrl + A
- Step 6: Right-click on any No value and press Delete
- Step 7: A dialogue box will open
- Step 8: Select Entire Row
- Step 9: Click OK
You will get the desired result of deleted no clients and only Yes clients will be reflected on your datasheet.
Another one option you can apply. Select your Yes/No column and then press Ctrl + F and then search for No value. You need to click on find all and then select all found results by pressing Ctrl + A.
Then click on the close button and you will see all your “No” values in Yes/ No Column is now selected and then right-click on any “No” values on the same column and then press the delete button. Then you will get another dialog box that will ask which one you want to delete. You need to choose the entire row and then press ok. See the below pictures.
As soon as you press OK it will delete the entire row where there is no values are present in your Yes/No column.
5. Delete entire row based on condition with Excel VBA
We will use the VBA code to delete the NO clients.
- Step 1: Go to Visual Basic page
- Step 2: Press Alt + F11
- Step 3: Click Insert > Module > New Module
- Step 4: Paste the code in the new module
- Step 5: Press F5 to run the code
- Step 6: The input box will appear to input the value you need to delete
- Step 7: Enter No
- Step 8: Click OK
You will get the desired result of deleted no clients and only Yes clients will be reflected on your datasheet. To get the exact details to read the below example.
Now we will use our VBA application to do the same task with code. To go to the visual basic page Press Alt + F11. Then click on Inset and then click on Module and in the new module paste the below code.
Sub deleterows()
Dim lr As Integer
Dim DeleteStr As String
DeleteStr = InputBox("Please write Yes or No?", , "Yes/No")
lr = Cells(Rows.Count, 5).End(xlUp).Row
For i = lr To 1 Step -1
If Cells(i, 5) = DeleteStr Then Rows(i & ":" & i).EntireRow.Delete
Next i
Range("B2").Select
End Sub
Now you run the code by pressing the F5 button or run button from the Visual basic page an input box will appear and will ask you which value you want to delete.
You can write yes or No and then press the ok button. If you write “No” value in the Input box then all the entire row where “No” values are there in column E will be deleted.
In my case, I have written “No” in the Input box that’s why it is deleting “No” values. You can also write “Yes” in the Input box to delete the rows where there are “Yes” values in your column E.
If you are puzzled on how to use VBA, I suggest that you take a look at our video-based Excel VBA tutorials (Well, we have created a practical VBA course out of it)
Now I will explain to you the code. We have written the below code in Module:
Sub deleterows()
Dim lr As Integer
Dim DeleteStr As String
DeleteStr = InputBox("Please write Yes or No?", , "Yes/No")
lr = Cells(Rows.Count, 5).End(xlUp).Row
For i = lr To 1 Step -1
If Cells(i, 5) = DeleteStr Then Rows(i & ":" & i).EntireRow.Delete
Next i
Range("B2").Select
End Sub
I have declared two variables one is ‘lr‘ and another one is ‘Deletelstr‘. Lr is helping me to find the last row of the column E. That means after this row there is no data exists. I have taken it as a variable because if tomorrow’s volumes of data increase my code will work.
Deletestr is another variable that helps to get what values I am giving in the Input box. Then I have run a loop through the entire data set and where it will get the value “Yes” or “No” (the values you are giving in the input box) the entire rows will be deleted.
Well, this was it from my side. I would love to hear if you have come across another challenge or an innovative solution developed. Comment below.
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12
i have 300 column in sheet1,but i need only 3 column from that to sheet2.out of that 3 column i need to filter for some values and the filtered values only need to display other values are not need …that are must hide or delete.. anybody help me………………
How can i delete rows based on multiple criteria in Excel VBA. This was helpful while working with single condition.
thanks… can i delete the columns in the same way… please advise
Yes