#1   Report Post  
Posted to microsoft.public.excel.misc
KK KK is offline
external usenet poster
 
Posts: 61
Default Delete duplicates?

I have the following code below. How can I include sheet "customers2" also in
the code?

Private Sub CommandButton99_Click()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets("Customers")
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step 1
If Application.CountIf(.Range("a1").EntireColumn, _
.Cells(iRow, "A").Value) 1 Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With

Unload Me

End Sub

thank you
--
kk
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Delete duplicates?

One way:

Option Explicit
Private Sub CommandButton99_Click()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

For Each wks In Worksheets(Array("customers", "customers2"))
With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step 1
If Application.CountIf(.Range("a1").EntireColumn, _
.Cells(iRow, "A").Value) 1 Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With
Next wks

Unload Me

End Sub


kk wrote:

I have the following code below. How can I include sheet "customers2" also in
the code?

Private Sub CommandButton99_Click()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets("Customers")
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step 1
If Application.CountIf(.Range("a1").EntireColumn, _
.Cells(iRow, "A").Value) 1 Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With

Unload Me

End Sub

thank you
--
kk


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
KK KK is offline
external usenet poster
 
Posts: 61
Default Delete duplicates?

Thanks Dave that works.
--
kk


"Dave Peterson" wrote:

One way:

Option Explicit
Private Sub CommandButton99_Click()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

For Each wks In Worksheets(Array("customers", "customers2"))
With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step 1
If Application.CountIf(.Range("a1").EntireColumn, _
.Cells(iRow, "A").Value) 1 Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With
Next wks

Unload Me

End Sub


kk wrote:

I have the following code below. How can I include sheet "customers2" also in
the code?

Private Sub CommandButton99_Click()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets("Customers")
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step 1
If Application.CountIf(.Range("a1").EntireColumn, _
.Cells(iRow, "A").Value) 1 Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With

Unload Me

End Sub

thank you
--
kk


--

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
delete duplicates in column not the row Wanna Learn Excel Discussion (Misc queries) 0 June 6th 07 04:08 PM
How to delete duplicates between workbooks? Jay Excel Worksheet Functions 1 January 23rd 07 04:06 PM
Delete Duplicates Lauren New Users to Excel 4 April 11th 06 06:46 AM
combine row and the delete duplicates bamamike Excel Discussion (Misc queries) 2 September 20th 05 05:16 PM
Delete duplicates Carter68 Excel Worksheet Functions 3 June 15th 05 01:01 AM


All times are GMT +1. The time now is 07:02 PM.

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

About Us

"It's about Microsoft Excel"