Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |