How to delete a row if its values are equal to another?

[mtbthepro]

Hello,
I have this code which should read all rows that begin with the word ACCOUNT#: which is followed by numbers like this: 1-026-31-00000-3342. The whole this looks like this ACCOUNT#: 1-026-31-00000-3342. If the code reads some row and there is another row identical to the row read in the beginning then the second row needs to get deleted.
Is it possible? This is what I have making so far but it doesn't work because the match function doesn't read that value.

Sub SECOND_MACRO_Delete_DuplicateAccount()
    
    Dim ws As Worksheet, row1 As Long, row2 As Long, rng As Long, n As Long, nlast As Long, rw As Range
    Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
    nlast = rw.Count
    
    For Each ws In Sheets
                    For r = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                        If ws.Cells(r, "A") Like "*ACCOUNT*" Then
                    row1 = WorksheetFunction.Match(ws.Cells(r, "A").Value, ws.Columns(1), 0)
                    row2 = WorksheetFunction.Match(ws.Cells(r, "A").Value, ws.Columns(1), 0)
                        If ws.Range("A" & row1).Value = ws.Range("A" & row2).Value Then
                        ws.Rows(row2).Delete
                        
                        End If
                    End If
            Next ws
          End Sub

 

[Legacy 396030]

Maybe this macro:

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 Rows(a).Delete
Next
End Sub

 

[mtbthepro]

Maybe this macro:

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 Rows(a).Delete
Next
End Sub

Hi there, that deletes all rows at are equal to eachother but is there a way to only delete rows if they contain ACCOUNT# and have matching numbers.

 

[Legacy 396030]

Use the concatenate to combine the columns first into column A. Then run the code

 

[mtbthepro]

Use the concatenate to combine the columns first into column A. Then run the code

that has been done sir. The columns are combined and set to go to get deleted if there is an extra one.

 

[Legacy 396030]

So what happens when you run the code?

 

[mtbthepro]

So what happens when you run the code?

It deletes anything that is matching to some other row. I don't want all rows getting deleting, only the ones that contain the word Account#: deleted if they match with another row that contains Account#:

 

[Legacy 396030]

Maybe it would be better to make it a table and sort the data however you wish.

 

[mtbthepro]

Maybe it would be better to make it a table and sort the data however you wish.

What do you mean by that?

 

[tonyyy]

You might consider the modified code below...

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 _
        And Left(Cells(a, 1), 8) = "ACCOUNT#" Then Rows(a).Delete
Next
End Sub

This will delete matching rows that start with "ACCOUNT#" and ignore all others.

Cheers,

tonyyy