Duplicate data in consecutive rows highlighted

sage123

New Member
Joined
Dec 2, 2018
Messages
13
I have a table in columns C - N. There are over 14000 rows of data. I need to have data where, in column K, there is data that is identical in consecutive rows. If I could have it highlighted then great, if I could have it automatically filtered out then even better! If filtering out is possible then I need to have it pull the whole row and not just the identical values in column K.

Any help you could give me would be greatly appreciated!!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
.
This is one method. Will delete the duplicate row.

Code:
Option Explicit


Sub DeleteDups()
     
    Dim x               As Long
    Dim LastRow         As Long
    Dim Cells           As Range
    Application.ScreenUpdating = False
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
        LastRow = LastRow
    End With
 
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("K1:K" & x), Range("K" & x).Text) > 1 Then
            Range("K" & x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
 
End Sub
 
Upvote 0
Hi Logit, I'm only interested in the consecutive rows with duplicate data - I want to keep them to. Highlighting rather than deleting is fine
 
Upvote 0
Code:
Option Explicit


Sub DeleteDups()
     
    Dim x               As Long
    Dim LastRow         As Long
    Dim Cells           As Range
    Application.ScreenUpdating = False
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
        LastRow = LastRow
     End With
 
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("K1:K" & x), Range("K" & x).Text) > 1 Then
            Range("K" & x).Interior.Color = vbYellow
        End If
    Next x
   
    Application.ScreenUpdating = True
 
End Sub
 
Upvote 0
.
This is a different version :

Code:
Option Explicit


Sub HighlightDups()
    Dim c As Range
    Dim x               As Long
    Dim LastRow         As Long
    Dim Cells           As Range
    Application.ScreenUpdating = False
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
        LastRow = LastRow
     End With
 
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("K1:K" & x), Range("K" & x).Text) > 1 Then
            Range("K" & x).EntireRow.Select
                For Each c In Selection
                    If c.Value <> "" Then
                        c.Interior.Color = vbYellow
                    End If
                Next c
            'Range("K" & x).Interior.Color = vbYellow
        End If
    Next x
    Range("A1").Select
    Application.ScreenUpdating = True
 
End Sub
 
Last edited:
Upvote 0
You can also do that with conditional formatting.
Select your data > Conditional Formatting > new rule > use a formula > use
=OR($K2=$K3,$K2=$K1)

select your format > Ok.
The formula assumes that the first row you selected was row 2
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top