Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
SITCFanTN
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Norman Jones
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
SITCFanTN
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Dave Peterson
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I delete all rows that contain no red highlighted data? Linda Hudock Excel Discussion (Misc queries) 7 May 22nd 06 09:09 AM
Data values WON'T DELETE in PivotTable drop-downs Natalie 1229 Excel Discussion (Misc queries) 3 March 27th 06 09:18 PM
Delete rows with no data Mindie Setting up and Configuration of Excel 1 November 30th 05 10:24 PM
Unique and duplicate data between 2 Excel worksheets Greg Excel Worksheet Functions 0 August 9th 05 12:03 AM
How do I find duplicate rows in a list in Excel, and not delete it Matthew in FL Excel Discussion (Misc queries) 2 June 15th 05 09:11 PM


All times are GMT +1. The time now is 11:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"