macro - Find duplicate in column - delete line

[skyport]

Hi everyone.

What I have is a situation where data is inputted into columns A to G. Each line of data will include a telephone number in Column D.

If possible, I would like to have a macro that searches the column D and when it finds a duplicate telephone number, it will delete the entire rows that contain the duplicate telephone numbers so those lines are completely gone leaving only the one line where that telephone number first appears in column D along with leaving of course all lines where that tel number was never listed in the first place.

Can this be done?

 

[skyport]

I had put it in the criteria window but when I hit OK it returns reference not valid

 

[Joe4]

Are you using "Advanced Filter"?

If so, then you want two cells in your Criteria section.
The first cell should be the title in the first row of the column you are searching.
And then the cell just below it is the value you want (1).
So those two cells should make up your criteria.

Take a look here is you need help with Advanced Filters.

 

[skyport]

Advanced filter is what I am using. Only two windows available to enter data

top - Range - which auto fills to $G$1:$G$6928 and I have also tried with the G as the column title as well

Second - Criteria I have tried 1 and "1" I get the same error

I have used filters before. Don't know why the problem here

 

[Joe4]

Advanced Filter work differently than Auto Filters. In the Criteria section, you have to enter a data range that contains the column name and criteria, like I described. Take a look at the link I gave you, or Google "Advanced Filters in Excel" for examples.

 

[skyport]

Sorry Joe, I've been to the tutorials, have used advanced filters before. It just is not filtering in this case. Maybe something else on the sheet is an issue. I can get the column G to properly identify the number of times a phone number comes up on the list. I just need a simpler way to delete the duplicate rows if there is one without doing it manually .

 

[Joe4]

Advanced filter is what I am using. Only two windows available to enter data

top - Range - which auto fills to $G$1:$G$6928 and I have also tried with the G as the column title as well

Second - Criteria I have tried 1 and "1" I get the same error

Unless they have drastically changed things since 2000, I still think you may be doing this wrong.
Does the second item in Advanced Filter say "Criteria" or "Criteria range"?
You cannot just enter the criteria you want in the "Criteria range" box. You have to set-it up on your sheet and reference it. I'll try to walk you through it.

1. Make sure you have a title row in column 1. If you do not have one, add one.
2. Our COUNTIF formula should be in column H. Let's put a column header of "Count" in cell H1.
3. Now, pick some column that is not being used. Let's say it is column M. In cell M1 enter the word "Count" (to match our header in cell H1), and in cell M2, enter the number 1. These two cells (M1 and M2) make up our criteria range.
4. Now click the Advanced Filter button.
5. In the "List Range" box, make sure your ENTIRE range is included. Based on your posts here, I would say that should be range: $A$1:$H$6928 (not just column G)
6. In the "Criteria Range" box, enter: $M$1:$M$2
7. Now you can elect to either Filter in place (which will just hide the unwanted rows), or Filter to another location (which will only copy over the rows meeting your criteria). If you want to Filter to another location, enter the cell address of the top-left corner where you want to put it to in the "Copy To" box.
8. Click "OK" and it will filter your records.

 

[skyport]

Okay, although I had to use column G instead of H and V instead of M, it did work now as you outlined and it can get the job done although it does cause some residual issues, which are my oversight in not anticipating them ahead of time.

Do you suppose there is any way to move the subtracted cells upward ( within just the range of data - in this case columns A-G instead of deleting the entire line. If not, I have to take the result from the filter concept and paste to a new page each time, which would be great to avoid if it's possible. This issue was my oversight not seeing the repercussion in advance. The problem is there are program steps for other procedures and functions in cells within columns H through U, which also delete under the filter concept. This would prevent me from maintaining one ongoing list.

As a last resort, I could reprogram all the stuff between Column H-U to some row in the thousands but it would be nice to avoid.

 

[Joe4]

A VBA solution would probably work. We can probably come up with some code, if that is an acceptable solution.
So, are you putting the COUNTIF formula in column G then?

 

[skyport]

I sure appreciate you sticking with me on this challenge. A VBA would be fine. Column G contains the countif formula and Column V the criteria range. To be safe, best not touch any other columns through AZ as they may contain important info.

The idea here is the data including the countif results in G would change when the cells shift up to eliminate the duplicate lines although columns H - AZ remain as is, in order to preserve the functions/data contained therein.

Also to your previous question that was in RED, for your ongoing fine expertise and knowledge, the second window in the advanced filter does say Criteria range and not just criteria for the Excel 2000 version here.

 

[Joe4]

OK. Using VBA, we can do it without having to physically place a COUNTIF formula on the sheet. VBA can calculate it on-the-fly.

So here is the code that will look for duplicate occurrences of the values in column D, and delete just columns A-G of those rows:

Sub MyDeleteCode()

    Dim myLastRow As Long
    Dim myRow As Long
    Dim myDataRange As Range
    Dim myDataCrit As Range
    
    Application.ScreenUpdating = False
    
'   Find last row in column D with data
    myLastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Loop through all cells in column D going backwards
    For myRow = myLastRow To 2 Step -1
        Set myDataRange = Range("D2:D" & myRow)
        Set myDataCrit = Cells(myRow, "D")
'       If value in D appears more than once in range above, delete that row, colums A-G
        If Application.WorksheetFunction.CountIf(myDataRange, myDataCrit) > 1 Then
            Range(Cells(myRow, "A"), Cells(myRow, "G")).Delete Shift:=xlUp
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub