Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delete duplicate data
Can Somebody please explain this code to me? I think it may meet my needs
but I'm just not sure. I want to delete the previous row if the data in column G is the same in 2 consecutive rows. Sub deleledupsinpreviousrows() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) = Cells(i, 1) Then Rows(i - 1).Delete Next End Sub |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delete duplicate data
Hi SITCFanTN,
Your code operates on duplicates in column A of the active sheet. Try the following minor adaptation: '============= Sub DeleleDupesInPreviousRows() Dim WB As Workbook Dim SH As Worksheet Dim i As Long Const col As String = "G" '<<==== CHANGE Set WB = Workbooks("YourBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE With SH For i = .Cells(Rows.Count, col).End(xlUp).Row To 2 Step -1 If .Cells(i - 1, col) = .Cells(i, col) Then .Rows(i - 1).Delete End If Next End With End Sub '<<============= --- Regards, Norman "SITCFanTN" wrote in message ... Can Somebody please explain this code to me? I think it may meet my needs but I'm just not sure. I want to delete the previous row if the data in column G is the same in 2 consecutive rows. Sub deleledupsinpreviousrows() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) = Cells(i, 1) Then Rows(i - 1).Delete Next End Sub |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delete duplicate data
Thanks so much Norman, one more question for you. Since I will be running
this code in a macro that is launched by clicking on an icon on the toolbar that I have created, do I have to specify the Workbook and worksheet in the code? Thanks so much. Joyce "Norman Jones" wrote: Hi SITCFanTN, Your code operates on duplicates in column A of the active sheet. Try the following minor adaptation: '============= Sub DeleleDupesInPreviousRows() Dim WB As Workbook Dim SH As Worksheet Dim i As Long Const col As String = "G" '<<==== CHANGE Set WB = Workbooks("YourBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE With SH For i = .Cells(Rows.Count, col).End(xlUp).Row To 2 Step -1 If .Cells(i - 1, col) = .Cells(i, col) Then .Rows(i - 1).Delete End If Next End With End Sub '<<============= --- Regards, Norman "SITCFanTN" wrote in message ... Can Somebody please explain this code to me? I think it may meet my needs but I'm just not sure. I want to delete the previous row if the data in column G is the same in 2 consecutive rows. Sub deleledupsinpreviousrows() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) = Cells(i, 1) Then Rows(i - 1).Delete Next End Sub |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delete duplicate data
You can either specify the workbook and worksheet or you can assume that the
user is running the code against the activesheet and just use that. If you use the Activesheet, then you wouldn't need the WB variable and this portion: Set WB = Workbooks("YourBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE With SH Becomes Set SH = ActiveSheet With SH SITCFanTN wrote: Thanks so much Norman, one more question for you. Since I will be running this code in a macro that is launched by clicking on an icon on the toolbar that I have created, do I have to specify the Workbook and worksheet in the code? Thanks so much. Joyce "Norman Jones" wrote: Hi SITCFanTN, Your code operates on duplicates in column A of the active sheet. Try the following minor adaptation: '============= Sub DeleleDupesInPreviousRows() Dim WB As Workbook Dim SH As Worksheet Dim i As Long Const col As String = "G" '<<==== CHANGE Set WB = Workbooks("YourBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE With SH For i = .Cells(Rows.Count, col).End(xlUp).Row To 2 Step -1 If .Cells(i - 1, col) = .Cells(i, col) Then .Rows(i - 1).Delete End If Next End With End Sub '<<============= --- Regards, Norman "SITCFanTN" wrote in message ... Can Somebody please explain this code to me? I think it may meet my needs but I'm just not sure. I want to delete the previous row if the data in column G is the same in 2 consecutive rows. Sub deleledupsinpreviousrows() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) = Cells(i, 1) Then Rows(i - 1).Delete Next End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete all rows that contain no red highlighted data? | Excel Discussion (Misc queries) | |||
Data values WON'T DELETE in PivotTable drop-downs | Excel Discussion (Misc queries) | |||
Delete rows with no data | Setting up and Configuration of Excel | |||
Unique and duplicate data between 2 Excel worksheets | Excel Worksheet Functions | |||
How do I find duplicate rows in a list in Excel, and not delete it | Excel Discussion (Misc queries) |