1
\$\begingroup\$

I have a code that will delete the row if the entire row is exactly the same. So if in row 1 I have 100,200,300,400,500,600 in column A,B,C,D,E,F respectively, and in row 3 I have the same thing, it will remove row 3.

Sub remove()
    Dim a As Long
    For a = Cells(Rows.Count, 1).End(xlUp).row To 1 Step -1
        If WorksheetFunction.CountIf(Range("A1:A" & a), Cells(a, 1)) > 1 Then
            If WorksheetFunction.CountIf(Range("B1:B" & a), Cells(a, 2)) > 1 Then
                If WorksheetFunction.CountIf(Range("C1:C" & a), Cells(a, 3)) > 1 Then
                    If WorksheetFunction.CountIf(Range("D1:D" & a), Cells(a, 4)) > 1 Then
                        If WorksheetFunction.CountIf(Range("E1:E" & a), Cells(a, 5)) > 1 Then
                            If WorksheetFunction.CountIf(Range("F1:F" & a), Cells(a, 6)) > 1 Then Rows(a).Delete
                        End If
                    End If
                End If
            End If
        End If
    Next
End Sub

However this code looks a little long and I was wondering if I could use a for loop but I have no idea on how to start. I am new to VBA coding which is why my methods could be a little hard coded. Any help is appreciated, Thank you!

(Edit) Here is a sample workbook that I have created. In this workbook I have a few names on the first column and the next few columns are the points they obtain on different days. However, there are entries that have the exact same entry row, meaning, the points and the name is exactly the same and this is what I want to remove. in the sample workbook, the name james have the 2 exact same entries and therefore, it will remove 1 of it. However Lan and Denise does not have the same entries hence it remains after running the code.

\$\endgroup\$
2
  • \$\begingroup\$ It isn't clear what you are trying to do. Please provide a valid dataset. A downloadable workbook would be even better. \$\endgroup\$
    – TinMan
    Oct 31, 2019 at 3:54
  • \$\begingroup\$ Hi TinMan, I have edited in a workbook and a more detailed explanation. Thank you for your help \$\endgroup\$
    – Vba noob
    Oct 31, 2019 at 6:00

1 Answer 1

1
\$\begingroup\$

The proper way to do this is use the Range.RemoveDuplicates method.

Here is the refactored macro created by clicking the Remove Duplicates button on the Data tab of the Ribbon. Array(1, 2, 3, 4, 5, 6) contains all the column numbers that need matching data for a row to be deleted.

Application.ScreenUpdating = False
Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlYes
\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.