Can this be done? If bn=b(n+1) and cn=c(n+1) then delete row (n+1)?
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? |
Can this be done? If bn=b(n+1) and cn=c(n+1) then delete row (n+1)
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 "John Smith" wrote: 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? |
Can this be done? If bn=b(n+1) and cn=c(n+1) then delete row (n+1)
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. -- Ken Hudson "John Smith" wrote: 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? |
Can this be done? If bn=b(n+1) and cn=c(n+1) then delete row(n+1)
Ken Hudson wrote:
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. |
Can this be done? If bn=b(n+1) and cn=c(n+1) then delete row(n+1)
Ken Hudson wrote:
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. |
All times are GMT +1. The time now is 04:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com