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

  • Thread starter Thread starter Arno
  • Start date Start date
A

Arno

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