Restrict Duplication Of A Whole Row When Entering New Data Using VBA Form

G

ggg111

New Member
Joined
Aug 10, 2019
Messages
16
Good Day!

I've been looking for a solution for this problem and I can't find any since I'm new to VBA. So I have a sheet that contains 12 columns and i wanted to use VBA Form when entering new data every row is supposed to be different from each other but it's columns can have repeated values, what i'm trying to do is restrict entry of new datas that is already in the sheet but as a whole row. I already tried the data validation but it only works in columns

here's a sample table for better understanding,
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]JOB[/TD]
[TD]NAME[/TD]
[TD]DATE[/TD]
[TD]TIME[/TD]
[TD]BUNDLE#[/TD]
[TD]COLOR[/TD]
[TD]OPERATION[/TD]
[TD]SIZE[/TD]
[TD]QUANTITY[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]COLORED T/S[/TD]
[TD]SEWING[/TD]
[TD]GASPAR NOE[/TD]
[TD]6/8/19[/TD]
[TD]7:00 AM[/TD]
[TD]035[/TD]
[TD]WHITE[/TD]
[TD]EDGING 1[/TD]
[TD]XL[/TD]
[TD]50[/TD]
[TD]0.40[/TD]
[/TR]
[TR]
[TD]COLORED T/S[/TD]
[TD]SEWING[/TD]
[TD]GASPAR NOE[/TD]
[TD]6/8/19[/TD]
[TD]7:00 AM[/TD]
[TD]035[/TD]
[TD]WHITE[/TD]
[TD]EDGING 2[/TD]
[TD]XL[/TD]
[TD]50[/TD]
[TD]0.50[/TD]
[/TR]
</tbody>[/TABLE]


as you can see, almost everything in the two rows is the same except the EDGING 1 and EDGING 2 and it's PRICE. I wanted my form to not accept the entry (like display a warning box or something) if what i'm trying to enter in the second row is identically the same as in the first row

like this,
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]JOB[/TD]
[TD]NAME[/TD]
[TD]DATE[/TD]
[TD]TIME[/TD]
[TD]BUNDLE#[/TD]
[TD]COLOR[/TD]
[TD]OPERATION[/TD]
[TD]SIZE[/TD]
[TD]QUANTITY[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]COLORED T/S[/TD]
[TD]SEWING[/TD]
[TD]GASPAR NOE[/TD]
[TD]6/8/19[/TD]
[TD]7:00 AM[/TD]
[TD]035[/TD]
[TD]WHITE[/TD]
[TD]EDGING 1[/TD]
[TD]XL[/TD]
[TD]50[/TD]
[TD]0.40[/TD]
[/TR]
[TR]
[TD]COLORED T/S[/TD]
[TD]SEWING[/TD]
[TD]GASPAR NOE[/TD]
[TD]6/8/19[/TD]
[TD]7:00 AM[/TD]
[TD]035[/TD]
[TD]WHITE[/TD]
[TD]EDGING 1[/TD]
[TD]XL[/TD]
[TD]50[/TD]
[TD]0.40[/TD]
[/TR]
</tbody>[/TABLE]



Thank you so much in advance!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
I assume that you want to test the LAST entered row to see if there is another row further up that is totally identical

with this data ...

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]ITEM CODE[/td][td]JOB[/td][td]NAME[/td][td]DATE[/td][td]TIME[/td][td]BUNDLE#[/td][td]COLOR[/td][td]OPERATION[/td][td]SIZE[/td][td]QUANTITY[/td][td]PRICE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]COLORED T/S[/td][td]SEWING[/td][td]GASPAR NOE[/td][td]
06/08/2019​
[/td][td]
7:00 AM​
[/td][td]
35​
[/td][td]WHITE[/td][td]EDGING 1[/td][td]XL[/td][td]
50​
[/td][td]
0.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]COLORED T/S[/td][td]SEWING[/td][td]GASPAR NOE[/td][td]
06/08/2019​
[/td][td]
7:00 AM​
[/td][td]
35​
[/td][td]WHITE[/td][td]EDGING 2[/td][td]XL[/td][td]
50​
[/td][td]
0.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]COLORED T/S[/td][td]SEWING[/td][td]GASPAR NOE[/td][td]
06/08/2019​
[/td][td]
7:00 AM​
[/td][td]
35​
[/td][td]WHITE[/td][td]EDGING 3[/td][td]XL[/td][td]
50​
[/td][td]
0.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]COLORED T/S[/td][td]SEWING[/td][td]GASPAR NOE[/td][td]
06/08/2019​
[/td][td]
7:00 AM​
[/td][td]
35​
[/td][td]WHITE[/td][td]EDGING 4[/td][td]XL[/td][td]
50​
[/td][td]
0.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]COLORED T/S[/td][td]SEWING[/td][td]GASPAR NOE[/td][td]
06/08/2019​
[/td][td]
7:00 AM​
[/td][td]
35​
[/td][td]WHITE[/td][td]EDGING 2[/td][td]XL[/td][td]
50​
[/td][td]
0.5​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Data[/td][/tr][/table]

.. VBA below detects that the last row is a duplicate of row 3, and offers user opportunity to delete that row

Code: 
Sub ggg()
    Dim ws As Worksheet, rng As Range, arr As Variant, combo As Variant, text As String
    Dim lr As Long, r As Long, c As Long
    Set ws = Sheets("Data")
    Set rng = ws.Range("A1").CurrentRegion
    arr = rng
    lr = UBound(arr)
    ReDim combo(1 To lr)
    
    For r = 1 To lr
        text = ""
        For c = 1 To rng.Columns.Count
            text = text & "|" & arr(r, c)
        Next
        combo(r) = text
    Next r
    
    For r = 1 To lr - 1
        If combo(lr) = combo(r) Then
            If MsgBox("delete row " & lr & " ?", vbYesNo, "Duplicate of row " & r) = vbYes Then ws.Rows(lr).EntireRow.Delete
            Exit Sub
        End If
    Next
End Sub

Notes
1. Values are placed in array
2. Values in array are concatenated to create a single string for each row (as in list below)
row1 |ITEM CODE|JOB|NAME|DATE|TIME|BUNDLE#|COLOR|OPERATION|SIZE|QUANTITY|PRICE
row2 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 1|XL|50|0.4
row3 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 2|XL|50|0.5
row4 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 3|XL|50|0.5
row5 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 4|XL|50|0.5
row6 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 2|XL|50|0.5
3. The LASTrow string is compared against every other row string until a match is found
 
Last edited:
Upvote 0
Hi Yongle!

Thank you so much for your answer! I appreciate it a lot but how do I use this code on a form? I wanted to use a form and restrict duplicate entries, here's how it looks
0
 
Upvote 0
Hi Yongle!

I already tried it using a form thank you so much. Also, I didn't want to delete the existing duplicate and then be replaced by my new entry, I just wanted to know if my new entry is a duplicate and then I won't let it be put in the data sheet. How do I execute this?
 
Upvote 0
Instead of comparing after putting the values in the worksheet
- create a concatenation of current userform control values
- the sequence of the concatenation needs to be the same as the sequence of the columns in the worksheet
- compare to the concatenation of each row

The code would look something like this:
Code: 
Private Sub CommandButtonXXX_Click()
    Dim ws As Worksheet, rng As Range, arr As Variant
    Dim lr As Long, r As Long, c As Long
    Dim textWS As String, textUF As String
    Set ws = Sheets("Data")
    Set rng = ws.Range("A1").CurrentRegion
    arr = rng
    lr = UBound(arr)

[I][COLOR=#006400]'concatenate current userform values ( PSEUDO CODE )[/COLOR][/I]

    For each (relevant) Control in your userform controls
       textUF = textUF & "|" & ThisControl.Value
    Next Control
[COLOR=#006400][I]
'concatenate worksheet values in each row and compare[/I][/COLOR]
    For r = 1 To lr
        textWS = ""
        For c = 1 To rng.Columns.Count
            textWS = textWS & "|" & arr(r, c)
        Next
        If textWs = textUF then GoTo SkipRowUpdate
    Next r

[COLOR=#006400]'update row values [/COLOR]
   'code to write userform values to worksheet goes here

Exit Sub

SkipRowUpdate:
       MsgBox "Duplicate of row " & r
        'whatever else needs to happen
End Sub
 
Last edited:
Upvote 0
Hi Yongle! Sorry for replying late! Thanks for the code but I'm having a hard time understanding everything may i know what's wrong with the code i made.

Code: 
Private Sub cmdTranfer_Click()
    Dim ws As Worksheet, rng As Range, arr As Variant
    Dim lr As Long, r As Long, c As Long
    Dim textWS As String, textUF As String
    Set ws = Sheets("Sheet1")
    Set rng = ws.Range("A2").CurrentRegion
    arr = rng
    lr = UBound(arr)

'concatenate current userform values ( PSEUDO CODE )

For lr = 2 To lr

textUF = textUF & "|" & TxtBox1.Text
textUF = textUF & "|" & TxtBox2.Text
textUF = textUF & "|" & TxtBox3.Text
textUF = textUF & "|" & TxtBox4.Text
textUF = textUF & "|" & TxtBox5.Text
textUF = textUF & "|" & TxtBox6.Text
textUF = textUF & "|" & TxtBox7.Text
textUF = textUF & "|" & TxtBox8.Text
textUF = textUF & "|" & TxtBox9.Text
textUF = textUF & "|" & TxtBox10.Text
textUF = textUF & "|" & TxtBox11.Text

    Next lr
    

'concatenate worksheet values in each row and compare
    For r = 2 To lr
        textWS = ""
        For c = 1 To rng.Columns.Count
            textWS = textWS & "|" & arr(r, c)
        Next
        If textWS = textUF Then GoTo SkipRowUpdate
    Next r

'update row values

lRow = Cells(Rows.Count, 1).End(xlUp).Row

Cells(lRow, 1).Value = TxtBox1.Text
Cells(lRow, 2).Value = TxtBox2.Text
Cells(lRow, 3).Value = TxtBox3.Text
Cells(lRow, 4).Value = TxtBox4.Text
Cells(lRow, 5).Value = TxtBox5.Text
Cells(lRow, 6).Value = TxtBox6.Text
Cells(lRow, 7).Value = TxtBox7.Text
Cells(lRow, 8).Value = TxtBox8.Text
Cells(lRow, 9).Value = TxtBox9.Text
Cells(lRow, 10).Value = TxtBox10.Text
Cells(lRow, 11).Value = TxtBox11.Text

Exit Sub

SkipRowUpdate:
       MsgBox "Duplicate of row " & r
        'whatever else needs to happen
End Sub
 
Upvote 0
Instead of this
Code: 
For lr = 2 To lr
     textUF = textUF & "|" & TxtBox1.Text
     textUF = textUF & "|" & TxtBox2.Text
    .....
     textUF = textUF & "|" & TxtBox11.Text
Next lr

use this method
Code: 
'declare another variable  (it must be declared as variant in this case)
    Dim ctrl As Variant
    
'loop through like this
    For Each ctrl In Array(TxtBox1, TxtBox2, TxtBox3, TxtBox4, TxtBox5, TxtBox6, TxtBox7, TxtBox8, TxtBox9, TxtBox10, TxtBox11)
        textUF = textUF & "|" & ctrl.Text
    Next ctrl
 
Upvote 0
the part " textWS = textWS & "|" & arr(r, c) " says "Error 2015" what would be the problem here?

Code: 
    For r = 2 To lr
        textWS = ""
        For c = 2 To rng.Columns.Count
            textWS = textWS & "|" & arr(r, c)
        
        Next
        If textWS = textUF Then GoTo SkipRowUpdate
    Next r
 
Upvote 0
Also how do I code the form where new entries that is not a duplicate will be put in the last new row of the data table?
 
Upvote 0
the part " textWS = textWS & "|" & arr(r, c) " says "Error 2015" what would be the problem here?
Click to expand...

Why did you change A1 to A2 in this line?

Code: 
Set rng = ws.Range("[COLOR=#ff0000]A1[/COLOR]").CurrentRegion
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
874
KRice
K
F
  • Question Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
388
freeb1893
F
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
904
Alex Blakenburg
A
Z
  • Solved
Drop down list using unique but with changed number of rows
Replies
5
Views
343
Fluff
Fluff
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
270
Skyybot
Skyybot

Forum statistics

Threads
1,223,445
Messages
6,172,184
Members
452,447
Latest member
willsing5130

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top