Delete duplicates?
 

I have the following code below. This one I got from Dave Paterson.
It works very well.
I have used this code in UserForm1 (commandbutton1), so when pressed it
closes the form after deleting duplicates. I have added a vbYesNo to
commandbutton1 so that now it ask if you wish to continue yes or no.
Selecting "No" it runs the code below, but when selecting "Yes" I cant get it
to run the code below to delete duplicates and leave the form open. It high
lights "Dim lastrow as Object" which is in commandbutton1 code. Any ideas?


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

--
WH99

Delete duplicates?
 

Inside the userform module...

Declare a new variable that you can toggle the way you want.

Option Explicit
Dim OkToContinue as boolean
Private Sub CommandButton1_Click()

oktocontinue = true
call CommandButton99_Click
oktocontinue = false

End sub
Private Sub CommandButton99_Click()
'your code

if oktocontinue = false then
unload me
end if
End Sub

Untested, uncompiled.

=========
I think I'd just remove the Unload Me code from the CommandButton99_Click() sub.

Let 'em click the cancel button (or X) to close the form.





WH99 wrote:

I have the following code below. This one I got from Dave Paterson.
It works very well.
I have used this code in UserForm1 (commandbutton1), so when pressed it
closes the form after deleting duplicates. I have added a vbYesNo to
commandbutton1 so that now it ask if you wish to continue yes or no.
Selecting "No" it runs the code below, but when selecting "Yes" I cant get it
to run the code below to delete duplicates and leave the form open. It high
lights "Dim lastrow as Object" which is in commandbutton1 code. Any ideas?


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

--
WH99

--

Dave Peterson

Delete duplicates?
 

Thanks Dave,
I went with your last advice....your right! why complicate things
Many thanks
--
WH99


"Dave Peterson" wrote:

Inside the userform module...

Declare a new variable that you can toggle the way you want.

Option Explicit
Dim OkToContinue as boolean
Private Sub CommandButton1_Click()

oktocontinue = true
call CommandButton99_Click
oktocontinue = false

End sub
Private Sub CommandButton99_Click()
'your code

if oktocontinue = false then
unload me
end if
End Sub

Untested, uncompiled.

=========
I think I'd just remove the Unload Me code from the CommandButton99_Click() sub.

Let 'em click the cancel button (or X) to close the form.





WH99 wrote:

I have the following code below. This one I got from Dave Paterson.
It works very well.
I have used this code in UserForm1 (commandbutton1), so when pressed it
closes the form after deleting duplicates. I have added a vbYesNo to
commandbutton1 so that now it ask if you wish to continue yes or no.
Selecting "No" it runs the code below, but when selecting "Yes" I cant get it
to run the code below to delete duplicates and leave the form open. It high
lights "Dim lastrow as Object" which is in commandbutton1 code. Any ideas?


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

--
WH99

--

Dave Peterson