Update row in Excel and move updated row to the next empty row without creating Duplicates

Hi

I'm working with a massive excel workbook. In the workbook there is a column with a number of item codes (Column A), On my userfrom there is a textbox that finds the item code and populates a load of textboxes on the userfrom with all infromation form that row. It then allows the user to change any of the textboxes he wants and he presses a command button (Add Details), the new updated version then finds the next empty row and goes there successfully.

The problem with this is that there are now 2 rows with the same item code, I wonder is it possible that when i click the command button Add Details that it would delete the previous row where the item code was and leave the new updated version.

Thanks

Hi peels2141

In a word - yes.

My initial thought was why put a new entry - why not just overwrite the original? If you let me see what you have, I can probably suggest something.

Regards
Alastair

Hi aydeegee,

Thanks for the reply, ya im open to any suggestions

here is my code for poplating the textboxes

Private Sub txtEditProductID_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Search As String
Dim target As Range

Search = txtEditProductID.Value
Worksheets("PRODUCT").Activate
     
Set target = Sheets("PRODUCT").Cells.Find(What:=Search, LookIn:=xlFormulas, lookat:=xlWhole)
     
If target Is Nothing Then

        MsgBox "Item does not Exist"
        
        txtEditProductID = ""
        txtEditID = ""
        txtEditUPC = ""
        txtEditCategory = ""
        txtEditName = ""
        txtEditProductInformationIE = ""
        txtEditProductInformationNI = ""
        
        txtEditUnitPriceIE = ""
        txtEditUnitPriceNI = ""
        txtEditManufacturer = ""
        txtEditWidth = ""
        txtEditDepth = ""
        txtEditHeight = ""
        txtEditFront_0 = ""
        txtEditLeft_0 = ""
        txtEditMax_High = ""
        
        txtEditProductID.SetFocus
Else
        txtEditID.text = target.Offset(0, 1).Value
        txtEditUPC.text = target.Offset(0, 4).text
        txtEditCategory.text = target.Offset(0, 3).Value
        txtEditName.text = target.Offset(0, 5).text
        txtEditProductInformationIE.text = target.Offset(0, 6).text
        txtEditProductInformationNI.text = target.Offset(0, 7).text
        
        txtEditUnitPriceIE.text = target.Offset(0, 12).text
        txtEditUnitPriceNI.text = target.Offset(0, 13).text
        txtEditManufacturer.text = target.Offset(0, 16).text
        txtEditWidth.text = target.Offset(0, 17).text
        txtEditDepth.text = target.Offset(0, 18).text
        txtEditHeight.text = target.Offset(0, 19).text
        txtEditFront_0.text = target.Offset(0, 20).text
        txtEditLeft_0.text = target.Offset(0, 21).text
        txtEditMax_High.text = target.Offset(0, 22).text
        
    End If
    End Sub

And then here is the code for the command button (Add Details)

Private Sub cmdAddDetails_Click()

Dim lngwriterow As Long                                                     'Declaring the Variable

Dim ws As Worksheet
Set ws = Worksheets("PRODUCT")                                              'Any Information written in will be sent to the worksheet (Product)

lngwriterow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row                                                 'Will go to the last figure entered in the worksheet (Product) and skip a row for new data to be entered

If lngwriterow < 13 Then lngwriterow = 13

   ws.Range("A" & lngwriterow) = txtEditProductID.Value                         'The data entered in the textbox named ProductID will be sent to column A in the next free row available
   ws.Range("B" & lngwriterow) = txtEditID.Value                                'The data entered in the textbox named ID will be sent to column B in the next free row available
   ws.Range("E" & lngwriterow) = txtEditUPC.Value                               'The data entered in the textbox named UPC will be sent to column D in the next free row available
   ws.Range("D" & lngwriterow) = txtEditCategory.Value                          'The data entered in the textbox named Category will be sent to column C in the next free row available
   ws.Range("F" & lngwriterow) = txtEditName.Value                              'The data entered in the textbox named Name will be sent to column E in the next free row available
   ws.Range("G" & lngwriterow) = txtEditProductInformationIE.Value              'The data entered in the textbox named ProductInfoIE will be sent to column F in the next free row available
   ws.Range("H" & lngwriterow) = txtEditProductInformationNI.Value              'The data entered in the textbox named ProductInfoNI will be sent to column G in the next free row available

Me.MultiPage1.Value = 1
    
End Sub

Thanks Again

Hi peels2141

So now you may have a duplicate entry. If the ProductID matches then will all other columns match? If not, do you want to keep the earliest, the latest or both?

Assuming that the other columns will match and you want to delete the latest, in column I or later, row 1 use the formula

=COUNTIF(A1:$A$1,A1)

Delete any row greater than 1 in this column.

Let me know if I have missed anything.

Regards
Alastair

Hi aydeegee

Some of the columns will vary but it might just be one or two, because i might have changed the price or something like that in the updated version, but I would want the whole row deleted anyway. I would want to keep the latest updated version and delete the earliest if possible?

Thanks for the Help again
Peels

Hi Peels

There may be a neater way, but the formula

=IFERROR(MATCH(A1,$A2:$A$10000,0),"")

entered on line 1 will highlight all lines where there is a later match. These lines may be deleted.

Regards
Alastsir

Hi Alastair

I put that fromula into the first line above the column and nothing happened?

Regards
Peels

My apologies - I omitted after "entered on line one" "and copied down to the end of your data"

Regards
Alastair

H Alastair

I put the formula in a free column at the end and it worked perfectly, it showed me where all the duplicates were so thank you very much.

Thanks Again
Peels

Glad it worked ok.

Regards
Alastair