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 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 Power Users Ebook

Be Smart at Excel | New Skills for New JOB

This eBook will help you to get Excel Skills & Boost your career

ARE YOU STRUGGLING WITH EXCEL FORMULAS?
Become a "Hero at Office"• 200+ Video Lessons • Real-life Case studies • Lifetime Access

 

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

  1. Delete the entire row based on No value:

    If you have a data sheet 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,

    • Create another column to keep track
    • Insert new Column ‘F’
    • Fill the column with serial number 1, 2, 3, …
    • Select entire data by Ctrl + A
    • Click on Go To Data > Sort By > Column E
    • Click OK
    • You will see No and Yes are grouped apart from each other.
    • Again go to sort, set sort by to Help Column and order to Smallest to Largest.
    • Click OK

    You will get the desired result of deleted no clients and only Yes clients will be reflected on your data sheet. But these are short steps, please read 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 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

    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.



    Now you sort again and this time, your sort by 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.

  2. Delete an entire row with Find Option:

    Follow the following steps:

    • Select your Yes/No column
    • Press Ctrl + F value
    • Search for No value
    • Click on Find All
    • Select all by pressing Ctrl + A
    • Right click on any No value and press Delete
    • A dialogue box will open
    • Select Entire Row
    • Click OK

    You will get the desired result of deleted no clients and only Yes clients will be reflected on your data sheet.

    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.

  3. Delete entire rows based on condition with Excel VBA:

    We will use VBA code to delete the NO clients.

    • Go to Visual Basic page
    • Press Alt + F11
    • Click Insert > Module > New Module
    • Paste the code in new module
    • Press F5 to run the code
    • Input box will appear to input the value you need to delete
    • Enter No
    • Click OK

    You will get the desired result of deleted no clients and only Yes clients will be reflected on your data sheet. To get the exact details read 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 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.

    Delete-entire-row-based-on-Excel-VBA

    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


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

    Get Started with free online Excel Macro Tutorial, No Technical background required.

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