Deleting rows

New Contributor

Hi Everyone,


I would like to know if it is possible for Excel to automatically delete all occurrences of  a row that contains the same text.

As an example if a a worksheet contained the following:









Again is it possible for Excel to locate all the occurrences of the word "Help" and delete all three occurrences in one go. 

6 Replies
You need VBA macro.


Do you really want to delete all three occurrences of "Help" or may one remain?

If you only want to delete the duplicates, there is a special function for this in the menu in the Data area, you will find "Remove Duplicates". Could this help you? It is not 100% automatic, because you have to click twice in the menu.



Thank you for your response.

I would like to delete all three occurrences of the word "help".

I used the above as an example.

In my actual spreadsheet there will be multiple duplications and if I can delete all different duplications that would be great.

The spreadsheet may look more like this:










I can sort the list so that all occurrences are together but I would still have to go through the spreadsheet A:Z to find them.


I was hoping that there was a function that would sort through the spreadsheet and find all duplicate rows and then delete all occurrences of the row weather it be in rows 1 and 2 or rows 501 and 502.





best response confirmed by Hans Vogelaar (MVP)

 Does your data reside in a single column?  If so, you can readily come up with a subset of the unique values, without sorting, by using a combination of COUNTIF and FILTER functions: 

The top two gray cells contain the formulas shown above them in their respective columns.  Copy the column B formula down through the last of the data. rows.


And perhaps that can be written without the use of the additional column (B).

My actual data resides over 6 columns.
I will try your suggestion and see if it works.
Thankyou for your assistance

I have tried out your suggestions and it works.

Again thankyou all for helping to solve my problem.