Can this be done? If bn=b(n+1) and cn=c(n+1) then delete row (n+1)?

[John Smith]

I would like to delete repetitive rows. The logic is like this:

If bn=b(n+1) and cn=c(n+1) then delete row (n+1)?

Can this be done easily?

 

[Guest]

I'm not sure I understand your criteria, but a quick and dirty VBA to get rid
of dirty rows would look something like below. Hope that helps you out some.
sub()
do until i > (total rows)
if range().offset(i,0).value = (your criteria)
range.entirerow.delete
i = i - 1
end if
i = i + 1
loop
end sub

 

[Guest]

Hi John,
Give this a try...

Option Explicit
Dim ILoop As Integer
Dim NumRows As Integer
Sub DelDupes()
Application.ScreenUpdating = False
NumRows = Range("B65536").End(xlUp).Row
For ILoop = NumRows To 2 Step -1
If Cells(ILoop, "B") = Cells(ILoop - 1, "B") And Cells(ILoop, "C") =
Cells(ILoop - 1, "C") Then
Rows(ILoop).Delete
End If
Next ILoop
Application.ScreenUpdating = True
End Sub

Press Alt-F11 to open the VB editor.
Go to Insert > Module, paste in this code and then run it.

 

[John Smith]

Hi John,
Give this a try...

Option Explicit
Dim ILoop As Integer
Dim NumRows As Integer
Sub DelDupes()
Application.ScreenUpdating = False
NumRows = Range("B65536").End(xlUp).Row
For ILoop = NumRows To 2 Step -1
If Cells(ILoop, "B") = Cells(ILoop - 1, "B") And Cells(ILoop, "C") =
Cells(ILoop - 1, "C") Then
Rows(ILoop).Delete
End If
Next ILoop
Application.ScreenUpdating = True
End Sub

Press Alt-F11 to open the VB editor.
Go to Insert > Module, paste in this code and then run it.

Thanks. I followed your instruction to run the program and got an
error message saying "compiler error: syntax error" and an arrow
pointed to "Sub DelDupes()". I can't image what is wrong with that
statement.

 

[John Smith]

Hi John,
Give this a try...

Option Explicit
Dim ILoop As Integer
Dim NumRows As Integer
Sub DelDupes()
Application.ScreenUpdating = False
NumRows = Range("B65536").End(xlUp).Row
For ILoop = NumRows To 2 Step -1
If Cells(ILoop, "B") = Cells(ILoop - 1, "B") And Cells(ILoop, "C") =
Cells(ILoop - 1, "C") Then
Rows(ILoop).Delete
End If
Next ILoop
Application.ScreenUpdating = True
End Sub

Press Alt-F11 to open the VB editor.
Go to Insert > Module, paste in this code and then run it.

It works like a charm. Thanks a lot.