#Delete duplicate rows in excel how to
#Delete duplicate rows in excel mac

I'm finding it hard to follow as i am not that advanced with excel macro programming.

i tried it your way, replaced the text string with my own but nothing at all happened in my document. Thank you for your interest in my problem. Loop While Not c Is Nothing And c.Row HdrKeepRow If Not c Is Nothing And c.Row HdrKeepRow Then Find(HdrText, LookIn:=xlValues, SearchDirection:=xlNext) Lr = Range('D' & Rows.Count).End(xlUp).Row You will also need to modify the HdrText line to include whatever the text is you are actually searching for, replacing the xyz in my code.Īnyway, give it a run and report back if there are any further issues. I am not sure if you want to delete the 4 header rows under the first header or not, so I have not done that. If the header to keep is not in row 1, just modify the HdrKeepRow line near the start of my code.

It does not search for bold/not bold just deletes instances other than row 1. When you’re done, you’ll notice that all remaining rows are unique values.This assumes the header you want to keep is in row 1. The reason you need to do this instead of pressing the Delete key on your computer is that it deletes the line and not just the content. Once you have selected all the rows you want to delete, right-click on one of the gray numbers and select Delete.

#Delete duplicate rows in excel mac

Now that you’ve identified all the lines with duplicate content, browse the document and hold Ctrl on Windows or Command on Mac while clicking the number of each repeating line, as shown below. To keep a copy of the copy, just paste the original text back into the first line that has been replaced by 1. Now you can easily identify which lines have duplicate content. You’ll notice that all values matching “duplicate” have been changed to “1”. After entering the value, press “Replace All”. In this example, we will use the number “1”. Now you need to enter a value in the “Replace with” box. Make sure all other settings match those shown in the image below. If you don’t select this option, you may inadvertently delete cells that you need to keep. The reason for this is that sometimes your word may appear in other cells with other words. Check the Match entire cell contents check box. Now that you’ve decided what you’re looking for, press Options. Once launched, you can paste the copied word into the Find what section by pressing Ctrl+V or Command+V. Click in the cell and press Ctrl+C on Windows or Command+C on Mac.Īfter copying the word you want to search for, you need to press Ctrl+H on Windows or Control+H on Mac to bring up the replace function. Once opened, you need to select a cell containing what you want to find and replace and copy it. You need to first open the spreadsheet you want to work on. In this case, we’ll use the simple replacement tool built into all Microsoft Office products. This method is great for smaller spreadsheets if you want to identify entire rows that are repeated. If you actually have headers in the first row instead of duplicates, you will only be left with a copy of the existing duplicates. If you want to delete the first row, in this case you have to delete it manually. This method automatically assumes that you have headers in your table. In this case, since the first duplicate was found in row 1, two are left. Now you need to check the “Only Unique Records” checkbox.Īfter you click OK, your document should have all but one duplicates removed. If you are using Excel 2003, click Data > Filters and select Advanced Filters. With the table selected, simply click the Data tab, and in the Sorting and Filtering section, click Advanced. In order to sort the spreadsheet, you need to first select all with Ctrl+A or Command+A, as shown earlier. Let’s start over by opening an Excel spreadsheet. The second tool you can use in Excel to identify and remove duplicates is the Advanced Filter.

#Delete duplicate rows in excel how to

Related: How to Easily Select Blocks of Cells in Excel Option 2 – Advanced Filtering in Excel In this case, all rows with duplicate information are deleted, except one, the details of deletion are displayed in a popup dialog. Now that the entire table is selected, you can simply press “OK” to remove all duplicates. Once you’ve done this, you’ll notice that the entire table is highlighted again, and the Columns section has changed from Duplicates to Columns A, B, and C. We’ll deselect the My Data Has Headers box.

In this case, we don’t have any headers because the table starts at row 1.