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

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" <SITCFanTN@discussions.microsoft.com> wrote in message
news:D1B6B790-6F66-4C84-AC97-C42792A0A12B@microsoft.com...
> 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
>

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" <SITCFanTN@discussions.microsoft.com> wrote in message
> news:D1B6B790-6F66-4C84-AC97-C42792A0A12B@microsoft.com...
> > 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
> >
>
>
>

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" <SITCFanTN@discussions.microsoft.com> wrote in message
> > news:D1B6B790-6F66-4C84-AC97-C42792A0A12B@microsoft.com...
> > > 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