Delete Rows that are the same as row directly above

Philly7040

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,624
Office Version
  1. 2016
Platform
  1. Windows
If you want to drink coffee while waiting, the use this VBA.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
@Zot any reason why you have looped through the data twice rather than once like
VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,624
Office Version
  1. 2016
Platform
  1. Windows
@Zot any reason why you have looped through the data twice rather than once like
VBA Code:
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 :unsure:
 

Forum statistics

Threads
1,141,814
Messages
5,708,735
Members
421,588
Latest member
Wawie

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
Top