fbpx

How to delete rows in excel based on a condition?

Yodalearning>Tutorials>How to delete rows in excel based on a condition?

In this blog, I will discuss how to delete rows in excel based on condition. Sometimes we get the dataset 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 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 VBA Tutorial Course OnlineLearn Excel VBA  (Beginner To Advanced)

If you want to be a master at Excel VBA Programming language for Excel 2007, then our Excel VBA macros tutorials will make it easier for you to access it in applications such as Microsoft Office. Come create your own Macros and rule in excel.

How to delete rows in excel based on certain condition:

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.

Data-set-Delete-entire-row-based-condition

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… Insert-Column-Delete-Entire-row-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. Sort-data-Delete-entire-row-on-Conditions

Recommended:

Delete Rows in Excel Based on Condition, Remove Password From Excel Using Excel VBA (Coding) or you can learn such more related tips & tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn how to create Data Analytic Chart & Graph. Learn More

Testimonial by spreadsheet guru

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. No-Values-Delete-entire-row-based-conditions 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.

Why don’t you do your free excel training with HD Videos available on Excel Tips & Tricks with Free Ebooks Download

Now you sort again and this time, your sort of column should be Help column and order should be smallest to largest.

Sort-data-by-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. output-After-Delete-entire-row-based-condition 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

Delete Rows in Excel Based on Condition, Remove Password From Excel Using Excel VBA (Coding) or you can learn much more related tips & tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn how to create Data Analytic Chart & Graph. Learn More

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.

Delete-an-entire-row-with-find-options

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. Find-No-Delete-entire-row As soon as you press OK it will delete the entire row where there is no values are present in your Yes/No column.

Learn Remove Password From Excel and Using Excel VBA (Coding) or you can learn such more tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn how to create Data Analytic Chart & Graph. Learn More

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.

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

Sub deleterows()

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. excel vba delete row 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. After-code-run-Delete-Entire-row 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)

Learn Remove Password From Excel and Using Excel VBA (Coding) or you can learn such more tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn how to create Data Analytic Chart & Graph. Learn More

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.

2 Responses
  1. revanthraj

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

  2. Evan

    How can i delete rows based on multiple criteria in Excel VBA. This was helpful while working with single condition.

Leave a Reply