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

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,
ITEM CODEJOBNAMEDATETIMEBUNDLE#COLOROPERATIONSIZEQUANTITYPRICE
COLORED T/SSEWINGGASPAR NOE6/8/197:00 AM035WHITEEDGING 1XL500.40
COLORED T/SSEWINGGASPAR NOE6/8/197:00 AM035WHITEEDGING 2XL500.50

<tbody>
</tbody>


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,
ITEM CODEJOBNAMEDATETIMEBUNDLE#COLOROPERATIONSIZEQUANTITYPRICE
COLORED T/SSEWINGGASPAR NOE6/8/197:00 AM035WHITEEDGING 1XL500.40
COLORED T/SSEWINGGASPAR NOE6/8/197:00 AM035WHITEEDGING 1XL500.40

<tbody>
</tbody>



Thank you so much in advance!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
A
B
C
D
E
F
G
H
I
J
K
1
ITEM CODEJOBNAMEDATETIMEBUNDLE#COLOROPERATIONSIZEQUANTITYPRICE
2
COLORED T/SSEWINGGASPAR NOE
06/08/2019​
7:00 AM​
35​
WHITEEDGING 1XL
50​
0.4​
3
COLORED T/SSEWINGGASPAR NOE
06/08/2019
7:00 AM
35
WHITEEDGING 2XL
50
0.5
4
COLORED T/SSEWINGGASPAR NOE
06/08/2019​
7:00 AM​
35​
WHITEEDGING 3XL
50​
0.5​
5
COLORED T/SSEWINGGASPAR NOE
06/08/2019​
7:00 AM​
35​
WHITEEDGING 4XL
50​
0.5​
6
COLORED T/SSEWINGGASPAR NOE
06/08/2019
7:00 AM
35
WHITEEDGING 2XL
50
0.5
Sheet: Data

.. 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?

Why did you change A1 to A2 in this line?

Code:
Set rng = ws.Range("[COLOR=#ff0000]A1[/COLOR]").CurrentRegion
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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