fbpx

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 condition:

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

So, in that case, 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

Select the entire set of data by pressing ctrl +A

Go to data and sort the data and the sort by column should be column E which indicates Yes/No.

Sort-data-Delete-entire-row-on-Conditions

Click OK

Conclusion: 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

Note:

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.

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

Delete an entire row with Find Option:

  1. Select your Yes/No column
  2. Press Ctrl + F
  3. Search for No value.
  4. Click on Find All
  5. Select All (Ctrl + A)
    Delete-an-entire-row-with-find-options
  6. Click on close button
  7. You will see all your “No” values in Yes/ No Column are now selected
  8. Right click on any “No” values on the same column
  9. Click on delete button.
  10. You will get another dialog box which will ask which one you want to delete.
  11. Choose the entire row and then press ok. See below pictures.
  12. Press OK

Results: It will delete the entire row where there is no values are present in your Yes/No column.

Find-No-Delete-entire-row

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)

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


downloads

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