Delete row/s with the same elements as the above row
 

Hello,

Is there a way to delete row/s that contain all cells with the same
text/element as the row above ?

I tried this syntax but I get run time error 13 - type mismatch

If c.Value < c.Offset(-1, 0).Value Then FOR THIS LINE I GET THE DEBUGGER


Sub delMatchedRows()
Dim lr As Long, x As Long
Dim c As Range
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
For Each c In Rows(i).Cells
If c.Value < c.Offset(-1, 0).Value Then HERE I GET THE DEBUGGER
x = x + 1
End If
Next
If x = 0 Then
Rows(i).Delete
End If
x = 0
Next
End Sub

Thank you !!

Delete row/s with the same elements as the above row
 

Hi Arno

Try this, created with Excel 2003:

Sub RemoveRepeatedRow()
Dim blnRemove As Boolean
Dim intCols As Integer
Dim intCol As Integer

ActiveSheet.UsedRange.Cells(2, 1).Select
intCols = ActiveSheet.UsedRange.Columns.Count - 1
Do
blnRemove = True
For intCol = 0 To intCols
If ActiveCell.Offset(0, intCol).Value < ActiveCell.Offset
(-1, intCol).Value Then
blnRemove = False
Exit For
End If
Next
If blnRemove Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)

End Sub


HTH,

Wouter

Delete row/s with the same elements as the above row
 

Hi Wouter,

this line is in error bocomes red

If ActiveCell.Offset(0, intCol).Value < ActiveCell.Offset
(-1, intCol).Value Then

can you please check thanks !

"RadarEye" wrote:

Hi Arno

Try this, created with Excel 2003:

Sub RemoveRepeatedRow()
Dim blnRemove As Boolean
Dim intCols As Integer
Dim intCol As Integer

ActiveSheet.UsedRange.Cells(2, 1).Select
intCols = ActiveSheet.UsedRange.Columns.Count - 1
Do
blnRemove = True
For intCol = 0 To intCols
If ActiveCell.Offset(0, intCol).Value < ActiveCell.Offset
(-1, intCol).Value Then
blnRemove = False
Exit For
End If
Next
If blnRemove Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)

End Sub


HTH,

Wouter

Delete row/s with the same elements as the above row
 

Hi Arno

This is one line of code.

If you change it to

If ActiveCell.Offset(0, intCol).Value < _
ActiveCell.Offset(-1, intCol).Value Then

it must work.

It works fine for me.

Wouter

Delete row/s with the same elements as the above row
 

Thank you for coming back !

"RadarEye" wrote:

Hi Arno

This is one line of code.

If you change it to

If ActiveCell.Offset(0, intCol).Value < _
ActiveCell.Offset(-1, intCol).Value Then

it must work.

It works fine for me.

Wouter

Delete row/s with the same elements as the above row
 

It works great - Many thanks - Appreciated !

"Arno" wrote:

Thank you for coming back !

"RadarEye" wrote:

Hi Arno

This is one line of code.

If you change it to

If ActiveCell.Offset(0, intCol).Value < _
ActiveCell.Offset(-1, intCol).Value Then

it must work.

It works fine for me.

Wouter