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!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,353
Office Version
365
Platform
Windows
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:

ggg111

New Member
Joined
Aug 10, 2019
Messages
16
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
 

ggg111

New Member
Joined
Aug 10, 2019
Messages
16
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?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,353
Office Version
365
Platform
Windows
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:

ggg111

New Member
Joined
Aug 10, 2019
Messages
16
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,353
Office Version
365
Platform
Windows
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
 

ggg111

New Member
Joined
Aug 10, 2019
Messages
16
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
 

ggg111

New Member
Joined
Aug 10, 2019
Messages
16
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?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,353
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,450
Messages
5,486,969
Members
407,575
Latest member
calc

This Week's Hot Topics

Top