Delete duplicate rows

[Emoncada]

How can I have something go through a spreadsheet and find any rows that are duplicate and if duplicate to delete one of them.
So IF A1:J1 is the same as A25:J25 then Delete Row 1.

[Emoncada]

Ok even better can i have it do this.
This is for a userform. Data goes into the form then when updated goes to the spreadsheet. Can I have the userform look a txtbox in the userform and at a column in the spreadsheet before it sends it to see if there is a match and if so give you a msgbox.
Telling you Msgbox = "A Match has been found do you wish do delete previous one?"

If Yes then to delete any row that begins with that match then send the userform data to the spreadsheet.
If No goes back to userform.

I know that's a lot I just really need for this to work.

[obriensj]

Hi,

Please try the attached macro.
It is a blank spreadsheet, you just copy your data into the blank worksheet (attached), highlight the cells you want to be deduped then go to tools, macro, macros and run macro.

Dont know if this helps much, better than nothing i suppose.

Cheers

Steve

[Emoncada]

well i need it to look at the entire row this apparenlty looks at just one cell.

[Emoncada]

reminder this needs to happen from the userform when a cmdbutton is clicked. If possible. unless someone has a better way or idea of doing it.

[grichey]

Do you know how many columns per row you are using? If it's fixed you could simply specify a hard code comparision like below (concept not code)

ie. where var is your row #,

if Avar=A(var+1) AND Bvar=B(var+1) AND etc etc
delete row var
else
next row.

[Emoncada]

Yeah A:K so 11 columns.
Let me see if i can make clear of my goal just incase.

I have a save/Print Button I want so when clicked before it sends data to spreadsheet it will check spreadsheet and look at one column first (ex Column A.) and if that column has an identical match with the txtboxOrder.Value then it will prompt msgbox ="A Match has been found do you wish do delete previous one?" If they select "yes" then it will delete all rows with that value. If they select "no" then it will just take them back to the userform. Can this be possible or is there another way around?

[unmarkedhelicopter]

obriensj gave you some good code that covered the basics, can't you just extend that to cater for the other 10 cells in your row ?

[Bob Phillips]

Yeah A:K so 11 columns.
Let me see if i can make clear of my goal just incase.

I have a save/Print Button I want so when clicked before it sends data to spreadsheet it will check spreadsheet and look at one column first (ex Column A.) and if that column has an identical match with the txtboxOrder.Value then it will prompt msgbox ="A Match has been found do you wish do delete previous one?" If they select "yes" then it will delete all rows with that value. If they select "no" then it will just take them back to the userform. Can this be possible or is there another way around?



Private Sub cmdOK_Click()
Dim mpLookup As String
Dim mpRange As Range
Dim mpCell As Range
Dim mpFirst As String

mpLookup = Me.TextBox1.Text
If Application.Match(mpLookup, Worksheets("Sheet1").Columns(1), 0) Then

If MsgBox("A Match has been found do you wish do delete previous one(s)?", _
vbYesNo) = vbYes Then

With Worksheets("Sheet1").Columns(1)

Set mpCell = .Find(mpLookup)
Set mpRange = mpCell
mpFirst = mpRange.Address

Do

Set mpCell = .FindNext(mpCell)
If Not mpCell Is Nothing Then

Set mpRange = Union(mpRange, mpCell)
End If
Loop Until mpCell Is Nothing Or mpCell.Address = mpFirst
End With

If Not mpRange Is Nothing Then mpRange.EntireRow.Delete
End If
End If
End Sub

[Emoncada]

This is what i got
Private Sub CmdPrintSave_Click()
Dim mpLookup As String
Dim mpRange As Range
Dim mpCell As Range
Dim mpFirst As String

mpLookup = Me.TxtOrdNum.Text
If Application.Match(mpLookup, Worksheets("Packing_Slip_Pim").Columns(1), 0) Then

If MsgBox("A Match has been found do you wish do delete previous one(s)?", _
vbYesNo) = vbYes Then

With Worksheets("Packing_Slip_Pim").Columns(1)

Set mpCell = .Find(mpLookup)
Set mpRange = mpCell
mpFirst = mpRange.Address

Do

Set mpCell = .FindNext(mpCell)
If Not mpCell Is Nothing Then

Set mpRange = Union(mpRange, mpCell)
End If
Loop Until mpCell Is Nothing Or mpCell.Address = mpFirst
End With

If Not mpRange Is Nothing Then mpRange.EntireRow.Delete
End If
End If
End Sub

It's giving me a runtime error '13'
Type mismatch

ON LINE
If Application.Match(mpLookup, Worksheets("Packing_Slip_Pim").Columns(1), 0) Then

any ideas why?

[Bob Phillips]

That is a type mismatch, which suggests that it cannot find the data type in column A. Is column A numeric, maybe try



If Application.Match(Val(mpLookup), Worksheets("Sheet1").Columns(1), 0) Then

[Emoncada]

well there is nothing in column A at the moment would that affect it. If nothing is in the column then it should just place it.

[Emoncada]

I had this code in for that cmdbutton if there is no match to put data on the spreadsheet can i use this code
Dim RowNext As Integer, i As Long, j As Long

'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
For i = 1 To 18
If Me.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next

For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 11) = "YES"
End With
Next
Application.Dialogs(xlDialogPrint).Show
End Sub
If so where would it fall under in your code

[Bob Phillips]

I feel as though I am working blind a bit, and guessing, but does this do what you want



Private Sub CmdPrintSave_Click()
Dim mpLookup As String
Dim mpRange As Range
Dim mpCell As Range
Dim mpFirst As String
Dim mpFind As Long

mpLookup = Me.TxtOrdNum.Text
On Error Resume Next
mpFind = Application.Match(mpLookup, Worksheets("Packing_Slip_Pim").Columns(1), 0)
On Error GoTo 0
If mpFind = 0 Then

InsertEm
Else

If MsgBox("A Match has been found do you wish do delete previous one(s)?", _
vbYesNo) = vbYes Then

With Worksheets("Packing_Slip_Pim").Columns(1)

Set mpCell = .Find(mpLookup)
Set mpRange = mpCell
mpFirst = mpRange.Address

Do

Set mpCell = .FindNext(mpCell)
If Not mpCell Is Nothing Then

Set mpRange = Union(mpRange, mpCell)
End If
Loop Until mpCell Is Nothing Or mpCell.Address = mpFirst
End With

If Not mpRange Is Nothing Then mpRange.EntireRow.Delete
End If
End If
End Sub

Sub InsertEm()
'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
For i = 1 To 18
If Me.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next

Dim RowNext As Integer, i As Long, j As Long
For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 11) = "YES"
End With
Next
Application.Dialogs(xlDialogPrint).Show
End Sub

[Emoncada]

Ok im getting a variable not defined error on

Sub InsertEm()
RowNext =

is hightlighted

[Emoncada]

This is the file sorry for not uploading it sooner. Thanks for your help!

[Bob Phillips]

That doesn't work, it tries to open a text file.

[Emoncada]

sorry xld i forgot to include it

[Emoncada]

in Userform_Initialize() just change
Open "S:\Packing Slip\MyData.txt" For Input As #1 ' Open file for input. to Open "C:\MyData.txt" For Input As #1

and put MyData.txt into your C: drive Thanks

[Emoncada]

OK I Fixed the little problem it had.
Sub InsertEm()
Dim RowNext As Integer, i As Long, j As Long
'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
For i = 1 To 18
If Me.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next

For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 11) = "YES"
End With
Next
Application.Dialogs(xlDialogPrint).Show
End Sub

but when prompted and select yes it deletes it from the spreadsheet but doesn't add the new one until you click again. Can that just delete and add the new data?

[figment]

try this

Private Sub CmdPrintSave_Click()
Dim mpFirst As String
Dim mpRange As Range, mpCell As Range
If UserForm1.CmbBoxDesc1.Text <> "" Then
With Worksheets("Packing Slip Pim").Range("A:A")
'On Error Resume Next
Set mpCell = .Find(UCase(TxtOrdNum.Value), LookIn:=xlValues, LookAt:=xlWhole)
On Error GoTo 0
If mpCell Is Nothing Then
Call InsertEm
Else

If MsgBox("A Match has been found do you wish do delete previous one(s)?", _
vbYesNo) = vbYes Then

Set mpRange = mpCell
mpFirst = mpRange.Address
Set mpCell = .FindNext(mpCell)
While Not mpCell Is Nothing And mpCell.Address <> mpFirst
Set mpRange = Union(mpRange, mpCell)
Set mpCell = .FindNext(mpCell)
Wend

If Not mpRange Is Nothing Then mpRange.EntireRow.Delete
Call InsertEm
End If
End If
End With
Else
'some error message for no info
End If
End Sub

Sub InsertEm()
Dim RowNext As Integer, i As Long, j As Long
'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
j = 0
For i = 1 To 18
If Me.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next

For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 11) = "YES"
End With
Next
Application.Dialogs(xlDialogPrint).Show
End Sub

[Emoncada]

How can I make this work for 3 Userforms. This works great for one but I am going to have 2 other Userforms that are identical to this first one. I need for them to send all data from the forms to the spreadsheet.

[Aussiebear]

What about making the Sub Public and calling it to each form?

[Emoncada]

How can I do that so it knows will send all the data to the spreadsheet and then know which forms to print?