delete duplicate data

[SITCFanTN]

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

 

[Norman Jones]

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
'<<=============

 

[SITCFanTN]

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

 

[Dave Peterson]

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: