Highlight equal row text

[Manolocs]

Hello, How to Highlight two consecutive equal text cells in a table ? I have to find, identify and manually delete multiple rows of an extensive table with 5 columns, the search for consecutive identical rows is needed by the column "A" only, the other columns maybe not equal. I tried already with conditional formatting but while I am deleting the entire row, the conditional missed a lot of identical cells.
Any idea how to make it easier?
Thanks in advance.

 

[Manolocs]

strange, do you mind in send me again the file but with your updated code?

 

[Fishboy]

It was linked in my last post (post #10) where I wrote the word HERE in caps.

 

[Manolocs]

It is strange, the spreadsheet I am testing has 600 rows with 6 columns, and I get that error, but if I use 10 rows of the same file the macro runs according.

 

[Fishboy]

It is strange, the spreadsheet I am testing has 600 rows with 6 columns, and I get that error, but if I use 10 rows of the same file the macro runs according.

Hmm, very odd. You would think if the code wasn't going to work it simply wouldn't work rather than be influenced by how many rows of data there are (unless we were talking like a stupidly high number of rows).

Are you able to share an example workbook with me to test with? If you can then you would need to upload a copy to a file hosting site such as drop box, one drive, google drive or similar, then share a link to the file in a forum post.

 

[Manolocs]

Hi FishBoy, sorry I was not available, I was in a MAC computer and did not work so I change to a PC and still not working same error, but when I run your workbook with your example is working. only with my data is not working

 

[Fishboy]

Are you able to share an example workbook with me to test with? If you can then you would need to upload a copy to a file hosting site such as drop box, one drive, google drive or similar, then share a link to the file in a forum post.

.

 

[Schroeder51]

You could use the following macro to highlight the duplicate rows:

Nice macro, Fishboy, thanks!

Do you think it can be tweaked to highlight all the duplicates EXCEPT the first one?

 

[Fishboy]

Nice macro, Fishboy, thanks!

Do you think it can be tweaked to highlight all the duplicates EXCEPT the first one?

Hi Schroeder51, thanks

I am sure it could probably be tweaked to do that but it is not as simple.

If you noticed in my first reply to Manolocs I also offered a "delete duplicate" solution which would always leave 1 copy of each "duplicate" in your final data set if that is any use to you:

Sub RemoveDupes()
' Defines variables
Dim x As Long, LastRow As Long, cRange As Range


' Defines LastRow as the last row of data based on column A
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


' Sets the check range as A1 to the last row of A
Set cRange = Range("A1:A" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the count of the cell value is more than 1 then...
        If Application.WorksheetFunction.CountIf(cRange, .Value) > 1 Then
            ' Delete that row
            .EntireRow.Delete
        End If
    End With
' Check next cell in check range
Next x


End Sub

 

[Manolocs]

Hi Fishboy, I realize the problem is, some of the cells have a space more/less with the text, this cause the macro be false, is there a way to make your code to overlook the spaces? Thanks a lot

 

[Fishboy]

Hi Fishboy, I realize the problem is, some of the cells have a space more/less with the text, this cause the macro be false, is there a way to make your code to overlook the spaces? Thanks a lot

Where is the extra space? Is it always in the same place (like at the end maybe)?