Delete Rows that are the same as row directly above

[Philly7040]

I have a data set of about 400,000 rows. I would like to delete every row where the value in column C is the same as the value in column C of the row directly proceeding that row. How can I do this?

 

[Philly7040]

Can you insert an additional column of data, say in Column Z, and in that column insert formula =if(C2=C1,"Delete","") and copy it down, then filter the sheet on Column Z where the value = "Delete" and delete those rows. Then delete Column Z and remove filter.

This worked perfectly. It did take me 10 minutes to drag the formula down 400,000 rows, but now I have what I need. Thanks.

 

[Zot]

If you want to drink coffee while waiting, the use this VBA.

Sub DelSimilar()

Dim n&, eRow&
Dim cell As Range, rngData As Range, rngUnion As Range

Application.ScreenUpdating = False

eRow = Range("C1").End(xlDown).Row
Set rngData = Range("C2", "C" & eRow)

For Each cell In rngData
    If Range("C" & cell.Row) = Range("C" & cell.Row - 1) Then
        Range("C" & cell.Row).Interior.ColorIndex = 3
    End If
Next

For Each cell In rngData
    If cell.Interior.ColorIndex = 3 Then
        If Not rngUnion Is Nothing Then
            Set rngUnion = Union(cell, rngUnion)
        Else
            Set rngUnion = cell
        End If
    End If
Next

rngUnion.Delete

End Sub

 

[Fluff]

@Zot any reason why you have looped through the data twice rather than once like

For Each cell In rngData
    If Range("C" & cell.Row) = Range("C" & cell.Row - 1) Then
        If rngUnion Is Nothing Then Set rngUnion = cell Else Set rngUnion = Union(cell, rngUnion)
    End If
Next

 

[Zot]

@Zot any reason why you have looped through the data twice rather than once like

For Each cell In rngData
    If Range("C" & cell.Row) = Range("C" & cell.Row - 1) Then
        If rngUnion Is Nothing Then Set rngUnion = cell Else Set rngUnion = Union(cell, rngUnion)
    End If
Next

So that Philly has enough time to finish his coffee ?

I woke up at 5am and asked myself what if I marked the rows to delete and then deleted those marked rows. That was how I wrote it. The code was supposed to be sent to Optimizing Dept but I was rushing to leave home before 7am

Yes you are right. I don't need to marked but just collect the range. Why did I do that? Still sleepy I guess