How to delete rows in excel based on a condition?

by | April 7th, 2017

In this blog, I will discuss how to delete rows in excel based on 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 which is not relevant or there are innumerable blank cells, which you need to delete to analyse 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.

We will discuss how to delete rows in excel based on certain condition:

  1. Delete the entire row based on No value

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 cannot 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 filter also but sometimes it also not possible.

Delete the entire row based on Yes/No value

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…

You will create another column which will help us to track the order - Delete entire row based on Condition

In column F, I have placed the serial number so that 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.

Now Select entire data And sort the data indicates Yes/no - Delete Entire row based on Condition

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 Home tab and then click on Delete icon on cell section and then press delete sheet rows.

After Sorting Select No values then click on Delete icon press deletesheet 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.

After Delete No values your data messed up then sort agin slect help column and order should be smallest to largest

As soon as you will press the ok button your data will be back in the same order like it was earlier. The serial number for No values obviously should be deleted. Your data now looks like below.

Final output shown after Delete entire row based conditions

Now you delete your help column to get back original set of records but here from your “No” is deleted. And the important part is that your data order remains the same.

Read more: MIS Sherlock Holmes in Excel:  Formula auditing

  1. Delete an entire row with Find Option

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 result by pressing Ctrl + A.

You can also Delete an entire row with the help of Find Options - How to delete rows in excel

Then click on close button and you will see all your “No” values in Yes/ No Column are 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 which will ask which one you want to delete. You need to choose the entire row and then press ok. See below pictures.

After Find No Select - How to delete rows in excel

As soon as you press OK it will delete the entire row where there is no values are present in your Yes/No column.

  1. Delete entire rows based on condition with Excel VBA

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 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.

Input box appear with help of Excel VBA and Write yes/No - How to delete rows in excel

You can write yes or No and then press 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.

Delete entire row based on Excel VBA - Final output
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 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

First of all, Download the Excel macro file with VBA codes ready to use.

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 variable as because if tomorrow volumes of data increase my code will work.

Deletestr is another variable which 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.

Get Started with free online excel macro tutorialNo Technical background required.

Tags:
error: Content is protected !!