Duplicate data in consecutive rows highlighted

[sage123]

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!!

 

[Logit]

.
This is one method. Will delete the duplicate row.

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

 

[sage123]

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

 

[Logit]

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

 

[Logit]

.
This is a different version :

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

 

[Fluff]

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